SA0120 : Consider using NOT EXISTS,EXCEPT or LEFT JOIN instead of the NOT IN predicate with a subquery

The topic describes the SA0120 analysis rule.

Message

Consider using NOT EXISTS,EXCEPT or LEFT JOIN instead of the NOT IN predicate with a subquery

Description

The rule check T-SQL code for NOT IN predicate with a subquery as it can be replaced by EXISTS subquery, EXCEPT or LEFT JOIN.

Consider reviewing the code as if the result list of the subquery provided to the NOT INT predicate produces a NULL, then all rows will be filtered and no rows will be returned because of the NULL comparison rules which require the use of IS NULL instead of = NULL for NULL comparison.

Verify the SELECT statement in the NOT IN subquery is filtering out the NULL results or consider transition to use a NOT EXISTS, EXCEPT, or LEFT JOIN pattern instead.

Note Note

The ability to compare to null using = NULL depends on the ANSI_NULLS setting.

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name.
A SELECT statement that uses WHERE column_name != NULL returns zero rows even if there are non-null values in column_name.

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Using NOT IN:

SQL
Copy
1SELECT * 
2FROM sys.database_principals p 
3WHERE p.principal_id NOT IN (SELECT o.principal_id 
4                                                         FROM sys.objects o)

Using NOT EXISTS:

SQL
Copy
1SELECT * 
2FROM sys.database_principals p 
3WHERE NOT EXISTS (SELECT * 
4                          FROM sys.objects o 
5                                  WHERE o.principal_id = p.principal_id)

How to fix

Review the query and verify the SELECT statement in the NOT IN subquery is filtering out the NULL results. Consider rewriting it to use NOT EXISTS,EXCEPT or LEFT JOIN instead of the NOT IN predicate with a subquery.

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
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1-- The IN predicate can be replaced with NOT EXISTS.
 2SELECT      *
 3FROM        table3
 4WHERE       table3.c1 NOT IN( SELECT      c1
 5                          FROM        table4 )
 6
 7-- The NOT IN predicate can be replaced with NOT EXISTS.
 8SELECT      *
 9FROM        table3
10WHERE       table3.c1 NOT IN( ( ( ( SELECT      c1
11                                FROM        table1 )
12                              UNION ALL
13                              ( SELECT      c2
14                                FROM        table2 ) ) ) )
15
16-- The NOT IN predicate will be ignored
17SELECT      *
18FROM        table3
19WHERE       table3.c1 NOT IN ( ( ( ( SELECT      c1
20                                FROM        table1 )
21                              UNION ALL
22                              ( SELECT      c2
23                                FROM        table2 ) ) ) ) -- IGNORE:SA0120
24
25
26-- The statements bellow will not trigger rule violations.
27SELECT      *
28FROM        table3
29WHERE       table3.c1 NOT IN( 1, 2, 3 )
30
31
32SELECT      *
33FROM        table3
34WHERE       table3.c1 IN( ( ( ( SELECT      c1
35                                FROM        table1 )
36                              UNION ALL
37                              ( SELECT      c2
38                                FROM        table2 ) ) ) )
39SELECT      *
40FROM        table3
41WHERE       table3.c1 IN( SELECT      c1
42                          FROM        table4 )
43
44SELECT      *
45FROM        table3
46WHERE       EXISTS( ( ( ( SELECT      c1
47                          FROM        table1
48                          WHERE       table1.c1 = table3.c1 )
49                        UNION ALL
50                        ( SELECT      c2
51                          FROM        table2
52                          WHERE       table2.c2 = table3.c1 ) ) ) )
53
54SELECT      *
55FROM        table3
56WHERE       EXISTS( SELECT      c1
57                    FROM        table4
58                    WHERE       table4.c2 = table3.c1 )
Analysis Results
  Message Line Column
1 SA0120 : Consider using NOT EXISTS,EXCEPT or LEFT JOIN instead of the NOT IN predicate with a subquery. 4 26
2 SA0120 : Consider using NOT EXISTS,EXCEPT or LEFT JOIN instead of the NOT IN predicate with a subquery. 10 26
See Also

Other Resources

Analysis Rules