Click or drag to resize

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:

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

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.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0005 : Non-ANSI outer join syntax.1120
2SA0005 : Non-ANSI outer join syntax.1223
3SA0005 : Non-ANSI outer join syntax.1321
See Also

Other Resources