SA0120 : Consider using NOT EXISTS,EXCEPT or LEFT JOIN instead of the NOT IN predicate with a subquery

Introduction

Using the NOT IN predicate with subqueries can lead to unexpected behavior if the subquery results contain NULL values.

Description

The use of the NOT IN predicate with subqueries can lead to unexpected behavior if the subquery results include NULL values. In SQL Server, comparing a value to NULL with = or != can lead to incorrect results because NULL represents unknown data. If one of the subquery results is NULL , NOT IN will filter out all rows from the outer query, potentially leading to no data being returned.

For example:

-- Example of problematic query using NOT IN
SELECT *
FROM sys.database_principals p
WHERE p.principal_id NOT IN (SELECT o.principal_id
                             FROM sys.objects o);

This query will return no rows if any NULL values exist in the subquery results, because every comparison with NULL becomes unknown.

Potential data loss: All rows are filtered out if any subquery result is NULL .
Future compatibility: SQL Server’s ANSI_NULLS setting affects this behavior, with the expectation that ANSI_NULLS will permanently be set to ON in future releases.

How to fix

To address the issues caused by using NOT IN with a subquery that may include NULL values, it is recommended to refactor the query. Instead, use NOT EXISTS , EXCEPT , or a LEFT JOIN to achieve the desired results while avoiding unexpected behavior due to NULL comparison.

Follow these steps to address the issue:

1.Determine if the subquery referenced in the NOT IN clause returns any NULL values. Identify the columns and tables involved.

2.Refactor the query to use NOT EXISTS , which checks for non-existence in a more robust manner:

        -- Refactored query using NOT EXISTS
        SELECT *
        FROM sys.database_principals p
        WHERE NOT EXISTS (
            SELECT 1
            FROM sys.objects o
            WHERE o.principal_id = p.principal_id
        );

3.Alternatively, consider using the EXCEPT operator for set-based comparison or a LEFT JOIN to filter out matches:

        -- Refactored query using EXCEPT
        SELECT p.*
        FROM sys.database_principals p
        EXCEPT
        SELECT p.*
        FROM sys.database_principals p
        JOIN sys.objects o ON p.principal_id = o.principal_id;

        -- Or using LEFT JOIN
        SELECT p.*
        FROM sys.database_principals p
        LEFT JOIN sys.objects o ON p.principal_id = o.principal_id
        WHERE o.principal_id IS NULL;

For example:

-- Example of corrected query using NOT EXISTS
SELECT *
FROM sys.database_principals p
WHERE NOT EXISTS (
    SELECT 1
    FROM sys.objects o
    WHERE o.principal_id = p.principal_id
);

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- The NOT IN predicate can be replaced with NOT EXISTS.
SELECT  *
FROM  table3
WHERE  table3.c1 NOT IN( SELECT  c1
                         FROM  table4 )

Analysis Results

	Message	Line	Column
1	SA0120 : Consider using NOT EXISTS,EXCEPT or LEFT JOIN instead of the NOT IN predicate with a subquery.	5	21

Analysis Rules