Skip to content

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:

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

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 Table1
LEFT 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.

8 minutes per issue.

Design Rules, Code Smells

There is no additional info for this rule.

-- non-ANSI outer join syntax used:
SELECT *
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID],
[Person].[PersonPhone] pp,
[Person].[PhoneNumberType] pnt,
[Person].[EmailAddress] ea
WHERE
pp.BusinessEntityID *= p.[BusinessEntityID] AND
pp.[PhoneNumberTypeID] *= pnt.[PhoneNumberTypeID] AND
p.[BusinessEntityID] *= ea.[BusinessEntityID]
-- ANSI outer join syntax used here:
SELECT *
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.BusinessEntityID = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON p.[BusinessEntityID] = ea.[BusinessEntityID];
GO
  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

Analysis Rules