SA0005 : Non-ANSI outer join syntax
Introduction
Section titled “Introduction”Non-ANSI outer join syntax can lead to compatibility issues and maintenance challenges in SQL Server.Use ANSI-standard join syntax for clarity and consistency.
Description
Section titled “Description”The problem with using non-ANSI outer join syntax-like *= and =* -is that it’s deprecated and might not be supported in future versions of SQL Server. This can lead to queries that are harder to understand, maintain, and troubleshoot.
For example:
-- Example of problematic non-ANSI outer joinSELECT * FROM Table1, Table2 WHERE Table1.Column1 *= Table2.Column2;This query uses the non-ANSI *= outer join syntax, which can be difficult to read and maintain. Transitioning to ANSI-standard syntax improves readability and ensures future compatibility.
-
Non-ANSI syntax is harder to read and understand, especially for those not familiar with older SQL conventions.
-
Future SQL Server updates may remove support for non-ANSI joins, leading to potential query failures.
How to fix
Section titled “How to fix”Ensure that all SQL queries use ANSI-standard OUTER JOIN clauses to improve code readability and future compatibility.
Follow these steps to address the issue:
1.Identify parts of your SQL code using non-ANSI outer join syntax, such as *= or =* .
2.Replace each occurrence of the non-ANSI outer join with the ANSI-standard LEFT JOIN , RIGHT JOIN , or FULL OUTER JOIN , depending on the desired outcome.
3.Ensure that ON clauses appropriately specify the join condition between tables, as the WHERE clause should no longer include the join logic.
4.Test the updated queries to verify that they produce the expected results and that performance remains satisfactory.
Example of corrected query using ANSI-standard join:
SELECT *FROM Table1LEFT JOIN Table2 ON Table1.Column1 = Table2.Column2;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”8 minutes per issue.
Categories
Section titled “Categories”Design Rules, Code Smells
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”-- non-ANSI outer join syntax used:SELECT *FROM [HumanResources].[Employee] eINNER JOIN [Person].[Person] pON p.[BusinessEntityID] = e.[BusinessEntityID],[Person].[PersonPhone] pp,[Person].[PhoneNumberType] pnt,[Person].[EmailAddress] eaWHEREpp.BusinessEntityID *= p.[BusinessEntityID] ANDpp.[PhoneNumberTypeID] *= pnt.[PhoneNumberTypeID] ANDp.[BusinessEntityID] *= ea.[BusinessEntityID]
-- ANSI outer join syntax used here:SELECT *FROM [HumanResources].[Employee] eINNER JOIN [Person].[Person] pON p.[BusinessEntityID] = e.[BusinessEntityID]LEFT OUTER JOIN [Person].[PersonPhone] ppON pp.BusinessEntityID = p.[BusinessEntityID]LEFT OUTER JOIN [Person].[PhoneNumberType] pntON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]LEFT OUTER JOIN [Person].[EmailAddress] eaON p.[BusinessEntityID] = ea.[BusinessEntityID];
GOAnalysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0005 : Non-ANSI outer join syntax. | 10 | 20 |
| 2 | SA0005 : Non-ANSI outer join syntax. | 11 | 23 |
| 3 | SA0005 : Non-ANSI outer join syntax. | 12 | 21 |