SA0057 : Consider using EXISTS predicate instead of IN predicate

The topic describes the SA0057 analysis rule.

Message

Consider using EXISTS predicate instead of IN predicate

Description

The rule check T-SQL code for IN predicate using a sub-query as they can be replaced by EXISTS predicate.

Using EXISTS predicate is often considered better than IN predicate, especially when NOT IN predicate is used.

How to fix

<para>Rewrite the query and replace the usages of IN or NOT IN predicates with EXISTS or NOT EXISTS predicates.</para>

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
CheckForNotInOnly

The parameter specifies if to check only for NOT IN predicate.

no
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
 1SELECT      *
 2FROM        table3
 3WHERE       table3.c1 IN( 1, 2, 3 )
 4
 5-- The IN predicate can be replaced with EXISTS.
 6SELECT      *
 7FROM        table3
 8WHERE       table3.c1 IN( ( ( ( SELECT      c1
 9                                FROM        table1 )
10                              UNION ALL
11                              ( SELECT      c2
12                                FROM        table2 ) ) ) )
13
14-- The IN predicate can be replaced with EXISTS.
15SELECT      *
16FROM        table3
17WHERE       table3.c1 NOT IN( SELECT      c1
18                          FROM        table4 )
19
20SELECT      *
21FROM        table3
22WHERE       table3.c1 IN( SELECT      c1
23                          FROM        table4 /*IGNORE:SA0057*/)
Analysis Results
  Message Line Column
1 SA0057 : Consider using EXISTS instead of the IN predicate. 8 22
2 SA0057 : Consider using NOT EXISTS instead of the NOT IN predicate. 17 26
See Also

Other Resources

Analysis Rules