SA0005 : Non-ANSI outer join syntax |
![]() |
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.

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:
1-- Example of problematic non-ANSI outer join 2SELECT * 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.

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:
-
Identify parts of your SQL code using non-ANSI outer join syntax, such as *= or =*.
-
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.
-
Ensure that ON clauses appropriately specify the join condition between tables, as the WHERE clause should no longer include the join logic.
-
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:
1SELECT * 2FROM Table1 3LEFT JOIN Table2 ON Table1.Column1 = Table2.Column2;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Code Smells

There is no additional info for this rule.

SQL
1-- non-ANSI outer join syntax used: 2SELECT * 3FROM [HumanResources].[Employee] e 4INNER JOIN [Person].[Person] p 5ON p.[BusinessEntityID] = e.[BusinessEntityID], 6[Person].[PersonPhone] pp, 7[Person].[PhoneNumberType] pnt, 8[Person].[EmailAddress] ea 9WHERE 10pp.BusinessEntityID *= p.[BusinessEntityID] AND 11pp.[PhoneNumberTypeID] *= pnt.[PhoneNumberTypeID] AND 12p.[BusinessEntityID] *= ea.[BusinessEntityID] 13 14 15-- ANSI outer join syntax used here: 16SELECT * 17FROM [HumanResources].[Employee] e 18INNER JOIN [Person].[Person] p 19ON p.[BusinessEntityID] = e.[BusinessEntityID] 20LEFT OUTER JOIN [Person].[PersonPhone] pp 21ON pp.BusinessEntityID = p.[BusinessEntityID] 22LEFT OUTER JOIN [Person].[PhoneNumberType] pnt 23ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID] 24LEFT OUTER JOIN [Person].[EmailAddress] ea 25ON p.[BusinessEntityID] = ea.[BusinessEntityID]; 26 27GO |

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 |
