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.

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:

SQL
Copy
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.

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:

SQL
Copy
1SELECT * 
2FROM Table1 
3LEFT JOIN Table2 ON Table1.Column1 = Table2.Column2;

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
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
See Also

Other Resources

Analysis Rules