SA0032 : Avoid using NOT IN predicate in the WHERE clause

Using the NOT IN predicate in SQL queries can severely degrade performance in SQL Server environments.

Description

When the NOT IN predicate is used in the WHERE clause, SQL Server’s query optimizer often defaults to a TABLE SCAN rather than an INDEX SEEK, even when filtering columns are indexed. This behavior can lead to inefficient query execution and increased resource consumption.

For example:

SQL
Copy
1-- Example of problematic query
2SELECT * FROM Users WHERE UserID NOT IN (SELECT UserID FROM SuspendedUsers);

In this query, the optimizer may choose a full scan of the Users table, negatively impacting performance, especially with large datasets.

  • Increased execution time due to scanning the entire table instead of using available indexes.

  • Higher resource utilization, affecting database server performance and causing potential slowdowns in other operations.

How to fix

Improve query performance by replacing the NOT IN predicate with more efficient alternatives.

Follow these steps to address the issue:

  1. Analyze the current use of NOT IN predicate in your query and identify the columns involved. Ensure these columns are properly indexed.

  2. Consider replacing NOT IN with NOT EXISTS, which often results in better performance.

  3. If applicable, use an IN predicate instead of NOT IN, or perform a LEFT JOIN and check for NULL values to achieve the desired filtering.

For example:

SQL
Copy
1-- Replace NOT IN with NOT EXISTS
2SELECT * 
3FROM Users u 
4WHERE NOT EXISTS (
5    SELECT 1 
6    FROM SuspendedUsers s 
7    WHERE u.UserID = s.UserID
8);

Scope

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

Parameters
Name Description Default Value
IgnoreColumnsFromTempTables

Ignore not equal comparison of columns of a temporary table or table variable.

yes
Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix
20 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1SELECT     *
 2FROM       Person.Contact AS c
 3JOIN       HumanResources.Employee AS e
 4ON         e.ContactID = c.ContactID
 5WHERE      EmployeeID NOT IN ( 10,20,30, 40 )
 6
 7GO
 8SELECT     FirstName ,
 9           LastName
10FROM       Person.Contact AS c
11JOIN       HumanResources.Employee AS e
12ON         e.ContactID = c.ContactID
13WHERE      EmployeeID NOT IN( SELECT     SalesPersonID
14                              FROM       Sales.SalesPerson
15                              WHERE      SalesQuota > 250000 )
16
17-- The above statement can be replaced with this one:
18
19SELECT     FirstName ,
20           LastName
21FROM       Person.Contact AS c
22JOIN       HumanResources.Employee AS e
23ON         e.ContactID = c.ContactID
24WHERE      EmployeeID IN( SELECT     SalesPersonID
25                          FROM       Sales.SalesPerson
26                          WHERE      SalesQuota <= 250000 )
27
28
29SELECT     FirstName ,
30           LastName
31FROM       Person.Contact AS c
32JOIN       HumanResources.Employee AS e
33ON         e.ContactID = c.ContactID
34WHERE      EmployeeID NOT IN /*IGNORE:SA0032*/ ( SELECT     SalesPersonID
35                              FROM       Sales.SalesPerson
36                              WHERE      SalesQuota > 250000 )
37
38
39SELECT     *
40FROM       Person.Contact AS c
41JOIN       HumanResources.Employee AS e
42ON         e.ContactID = c.ContactID
43WHERE      EmployeeID NOT IN ( 10,20,30, 40 ) /*IGNORE:SA0032*/
Analysis Results
  Message Line Column
1 SA0032 : Avoid using NOT IN predicate in the WHERE clause. 5 22
2 SA0032 : Avoid using NOT IN predicate in the WHERE clause. 13 22
See Also

Other Resources

Analysis Rules