SA0005 : Non-ANSI outer join syntax

The topic describes the SA0005 analysis rule.

Message

Non-ANSI outer join syntax

Description

This rule checks for the use of non-ANSI outer joins (*= and =* syntax).

It is recommended that outer joins use the ANSI specified syntax, for example:

SQL
Copy
1SELECT     tab1.c1,
2           tab3.c2
3FROM       tab1
4LEFT OUTER JOIN tab2
5ON         tab1.c3 = tab2.c3
6RIGHT JOIN tab3
7           LEFT OUTER JOIN tab4
8           ON         tab3.c1 = tab4.c1
9ON         tab2.c3 = tab4.c3

How to fix

Replace the non-ANSI outer join usages with ANSI-Standard OUTER JOIN clauses.

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 
 3*
 4FROM [HumanResources].[Employee] e
 5INNER JOIN [Person].[Person] p
 6ON p.[BusinessEntityID] = e.[BusinessEntityID],
 7[Person].[PersonPhone] pp,
 8[Person].[PhoneNumberType] pnt,
 9[Person].[EmailAddress] ea
10WHERE
11pp.BusinessEntityID *= p.[BusinessEntityID] AND 
12pp.[PhoneNumberTypeID] *= pnt.[PhoneNumberTypeID] AND
13p.[BusinessEntityID] *= ea.[BusinessEntityID]
14
15
16-- ANSI outer join syntax used here:
17SELECT
18*
19FROM [HumanResources].[Employee] e
20INNER JOIN [Person].[Person] p
21ON p.[BusinessEntityID] = e.[BusinessEntityID]
22LEFT OUTER JOIN [Person].[PersonPhone] pp
23ON pp.BusinessEntityID = p.[BusinessEntityID]
24LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
25ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
26LEFT OUTER JOIN [Person].[EmailAddress] ea
27ON p.[BusinessEntityID] = ea.[BusinessEntityID];
28
29-- non-ANSI outer join syntax used:
30SELECT -- IGNORE:SA0005(STATEMENT)
31 p.*,pp.[PhoneNumber]
32FROM
33[Person].[Person] p,
34[Person].[PersonPhone] pp
35WHERE
36pp.BusinessEntityID *= p.[BusinessEntityID]
37
38SELECT
39 p.*,pp.[PhoneNumber]
40FROM
41[Person].[Person] p,
42[Person].[PersonPhone] pp
43WHERE
44pp.BusinessEntityID *= p.[BusinessEntityID] -- IGNORE:SA0005(LINE)
Analysis Results
  Message Line Column
1 SA0005 : Non-ANSI outer join syntax. 11 20
2 SA0005 : Non-ANSI outer join syntax. 12 23
3 SA0005 : Non-ANSI outer join syntax. 13 21
See Also

Other Resources

Analysis Rules