Click or drag to resize

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.

Scope

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

Parameters
NameDescriptionDefault 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.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

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

 MessageLineColumn
1SA0057 : Consider using EXISTS instead of the IN predicate.822
2SA0057 : Consider using NOT EXISTS instead of the NOT IN predicate.1726
See Also

Other Resources