SA0006 : Non-ANSI inner join syntax

Non-ANSI join syntax reduces readability, maintainability, and compatibility with modern SQL standards.

Description

Using non-ANSI join syntax can lead to confusion and maintainability challenges. The ANSI-standard JOIN syntax separates join logic from filtering logic, which significantly improves the clarity and readability of SQL queries.

Example of old-style join syntax:

SQL
Copy
1SELECT * FROM Orders, Customers
2WHERE Orders.CustomerID = Customers.CustomerID;

Here, the join condition Orders.CustomerID = Customers.CustomerID is mixed in the WHERE clause, along with any filtering conditions. This can obscure what logic pertains to joining versus filtering, making the query harder to understand and maintain.

  • Non-ANSI syntax complicates query readability by combining join and filtering conditions.

  • Maintaining or updating queries becomes more error-prone due to unclear separation of logic.

How to fix

Replace non-ANSI join syntax with ANSI-standard JOIN clauses to improve query clarity and maintainability.

Follow these steps to address the issue:

  1. Identify the parts of your query where non-ANSI join syntax is used. Look for join conditions in the WHERE clause that connect tables using equality.

  2. Convert these join conditions to use the ANSI-standard JOIN clauses. This involves moving the join condition to an ON clause following the JOIN keyword.

  3. Verify that any remaining conditions in the WHERE clause are solely for filtering purposes, maintaining a clear distinction between joining logic and filtering logic.

  4. Test the modified query to ensure it produces the expected results and behaves consistently with its original intent.

Example of ANSI-Standard join syntax:

SQL
Copy
1SELECT Orders.*, Customers.* 
2FROM Orders 
3JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

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, Deprecated Features, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1-- non-ANSI inner join syntax: 
 2SELECT     a.au_id ,
 3           t.titlr e
 4FROM       titles AS t ,
 5           authors AS a ,
 6           titleauthor AS ta
 7WHERE      a.au_id = ta.au_id
 8       AND ta.title_id = t.title_id
 9       AND t.title LIKE 'Example%'
10
11-- ANSI inner join syntax: 
12
13SELECT     a.au_id ,
14           t.title
15FROM       authors AS a
16INNER JOIN titleauthor AS ta
17ON         a.au_id = ta.au_id
18INNER JOIN titles AS t
19ON         ta.title_id = t.title_id
20WHERE      t.title LIKE 'Example%'
21
22
23SELECT     a.au_id ,
24           t.title
25FROM       authors AS a
26INNER JOIN titleauthor AS ta
27ON         a.au_id = ta.au_id
28INNER JOIN titles AS t
29ON         ta.title_id = t.title_id
30WHERE      t.title LIKE 'Example%' -- IGNORE:SA0006(statement)
Analysis Results
  Message Line Column
1 SA0006 : Non-ANSI inner join syntax. 4 0
See Also

Other Resources

Analysis Rules