Click or drag to resize

SA0032 : Avoid using NOT IN predicate in the WHERE clause

The topic describes the SA0032 analysis rule.

Message

Avoid using NOT IN predicate in the WHERE clause

Description

Using NOT IN predicate in the WHERE clause generally performs badly, because the SQL Server optimizer has to use a TABLE SCAN instead of an INDEX SEEK even the filtering columns are covered by index.

Consider using one of the following options instead all of which offer better performance:

- EXISTS or NOT EXISTS

- IN

- Perform LEFT OUTER JOIN and check for a NULL

Scope

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

Parameters
NameDescriptionDefault Value
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:SA0032

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1SELECT     FirstName ,
 2           LastName
 3FROM       Person.Contact AS c
 4JOIN       HumanResources.Employee AS e
 5ON         e.ContactID = c.ContactID
 6WHERE      EmployeeID NOT IN( SELECT     SalesPersonID
 7                              FROM       Sales.SalesPerson
 8                              WHERE      SalesQuota > 250000 )
 9
10-- The above statement can be replaced with this one:
11
12SELECT     FirstName ,
13           LastName
14FROM       Person.Contact AS c
15JOIN       HumanResources.Employee AS e
16ON         e.ContactID = c.ContactID
17WHERE      EmployeeID IN( SELECT     SalesPersonID
18                          FROM       Sales.SalesPerson
19                          WHERE      SalesQuota <= 250000 )
20
21
22SELECT     FirstName ,
23           LastName
24FROM       Person.Contact AS c
25JOIN       HumanResources.Employee AS e
26ON         e.ContactID = c.ContactID
27WHERE      EmployeeID NOT IN /*IGNORE:SA0032*/ ( SELECT     SalesPersonID
28                              FROM       Sales.SalesPerson
29                              WHERE      SalesQuota > 250000 )
30
31
32SELECT     *
33FROM       Person.Contact AS c
34JOIN       HumanResources.Employee AS e
35ON         e.ContactID = c.ContactID
36WHERE      EmployeeID NOT IN ( 10,20,30, 40 ) /*IGNORE:SA0032*/

Analysis Results

 MessageLineColumn
1SA0032 : Avoid using NOT IN predicate in the WHERE clause.622
See Also

Other Resources