SA0032 : Avoid using NOT IN predicate in the WHERE clause

Introduction

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:

-- Example of problematic query
SELECT * 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:

-- Replace NOT IN with NOT EXISTS
SELECT *
FROM Users u
WHERE NOT EXISTS (
    SELECT 1
    FROM SuspendedUsers s
    WHERE u.UserID = s.UserID
);

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.

Additional Information

There is no additional info for this rule.

Example Test SQL

SELECT     *
FROM       Person.Contact AS c
JOIN       HumanResources.Employee AS e
ON         e.ContactID = c.ContactID
WHERE      EmployeeID NOT IN ( 10,20,30, 40 )

GO
SELECT     FirstName ,
           LastName
FROM       Person.Contact AS c
JOIN       HumanResources.Employee AS e
ON         e.ContactID = c.ContactID
WHERE      EmployeeID NOT IN( SELECT     SalesPersonID
                              FROM       Sales.SalesPerson
                              WHERE      SalesQuota > 250000 )

-- The above statement can be replaced with this one:

SELECT     FirstName ,
           LastName
FROM       Person.Contact AS c
JOIN       HumanResources.Employee AS e
ON         e.ContactID = c.ContactID
WHERE      EmployeeID IN( SELECT     SalesPersonID
                          FROM       Sales.SalesPerson
                          WHERE      SalesQuota <= 250000 )


SELECT     FirstName ,
           LastName
FROM       Person.Contact AS c
JOIN       HumanResources.Employee AS e
ON         e.ContactID = c.ContactID
WHERE      EmployeeID NOT IN /*IGNORE:SA0032*/ ( SELECT     SalesPersonID
                              FROM       Sales.SalesPerson
                              WHERE      SalesQuota > 250000 )


SELECT     *
FROM       Person.Contact AS c
JOIN       HumanResources.Employee AS e
ON         e.ContactID = c.ContactID
WHERE      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

Analysis Rules