SA0006 : Non-ANSI inner join syntax
Introduction
Section titled “Introduction”Non-ANSI join syntax reduces readability, maintainability, and compatibility with modern SQL standards.
Description
Section titled “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:
SELECT * FROM Orders, CustomersWHERE 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
Section titled “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:
SELECT Orders.*, Customers.*FROM OrdersJOIN Customers ON Orders.CustomerID = Customers.CustomerID;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, Deprecated Features, 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 inner join syntax:SELECT a.au_id , t.titlr eFROM titles AS t , authors AS a , titleauthor AS taWHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND t.title LIKE 'Example%'
-- ANSI inner join syntax:
SELECT a.au_id , t.titleFROM authors AS aINNER JOIN titleauthor AS taON a.au_id = ta.au_idINNER JOIN titles AS tON ta.title_id = t.title_idWHERE t.title LIKE 'Example%'
SELECT a.au_id , t.titleFROM authors AS aINNER JOIN titleauthor AS taON a.au_id = ta.au_idINNER JOIN titles AS tON ta.title_id = t.title_idWHERE t.title LIKE 'Example%' -- IGNORE:SA0006(statement)Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0006 : Non-ANSI inner join syntax. | 4 | 0 |