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

RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0057

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