NOT IN, NOT EXISTS, and NULL: SQL Server anti-join mistakes

A report asks for customers who have never placed an order. It works for months, then suddenly returns nothing even though many customers are still inactive. The query has not changed. One imported order with a missing customer reference is enough to expose the problem if the report uses NOT IN.

SQL evaluates comparisons with NULL using unknown, alongside true and false. A WHERE clause retains only rows for which its condition is true. It does not retain unknown. Understanding that rule is more useful than memorizing a blanket recommendation to replace every IN expression.

Reproduce the unexpected result

The first statement below returns no customer IDs. The NOT EXISTS version returns customers 2 and 3. The left join also returns 2 and 3 because it tests a column that cannot be NULL in a real matching order. The final query reports three orders but only two non-null customer references.

DECLARE @Customers TABLE(CustomerId int NOT NULL PRIMARY KEY);
DECLARE @Orders TABLE(OrderId int NOT NULL, CustomerId int NULL);
INSERT @Customers VALUES (1),(2),(3);
INSERT @Orders VALUES (10,1),(11,NULL),(12,1);

SELECT c.CustomerId
FROM @Customers AS c
WHERE c.CustomerId NOT IN
    (SELECT o.CustomerId FROM @Orders AS o);

SELECT c.CustomerId
FROM @Customers AS c
WHERE NOT EXISTS
(
    SELECT 1 FROM @Orders AS o
    WHERE o.CustomerId=c.CustomerId
);

SELECT c.CustomerId
FROM @Customers AS c
LEFT JOIN @Orders AS o ON o.CustomerId=c.CustomerId
WHERE o.OrderId IS NULL;

SELECT COUNT(*) AS AllOrders,
       COUNT(CustomerId) AS OrdersWithCustomer
FROM @Orders;

For customer 2, the NOT IN condition effectively requires comparisons against every value in the subquery. The comparison with 1 succeeds for inequality, but the comparison with NULL is unknown. The combined condition is not true. Customer 1 is excluded by the actual match, leaving no rows overall.

Filtering NULL out of the subquery can make NOT IN correct for this particular non-null outer key. That is a legitimate repair if nullability is part of a documented contract. It is less robust when a future change introduces a nullable outer expression or the business meaning of missing values changes. NOT EXISTS states the absence-of-a-match requirement more directly.

Choose the relationship you actually mean

NOT EXISTS checks whether any qualifying matching row is present. Multiple orders for customer 1 do not duplicate customer 2 in the result. It does not, however, make NULL equal to NULL. If the outer key were nullable and the business considered two missing values a match, ordinary equality in the correlated subquery would not implement that rule.

The left-join form needs equal care. Testing a nullable column from the right table can mistake a real match for an unmatched row. In this example OrderId is declared NOT NULL, so its null extension proves that no matching order exists. Testing a nullable delivery date would not provide the same guarantee.

Predicate placement also changes meaning. To find customers with no paid orders, put the paid-state condition inside NOT EXISTS, or inside the ON clause of the corresponding left join. Filtering the right table's paid state in the outer WHERE can remove the null-extended rows you were trying to find. Write a tiny dataset with unpaid orders, paid orders, and no orders before optimizing.

Verify semantics before comparing plans

For large tables, an index beginning with Orders.CustomerId can support the existence check. If the condition includes paid state, consider the actual distribution and whether a filtered or composite index fits that requirement. The optimizer can implement different syntactic forms with similar anti-semi-join operators; syntax alone does not establish which is faster.

EXCEPT is another useful operation, but it has set semantics, removes duplicates, and treats NULLs as equal for distinct comparison. It is not a transparent replacement when multiplicity or nullable matching is part of the output contract. Likewise, adding DISTINCT after an accidental many-to-many join may conceal a modeling mistake while introducing extra work.

A practical test matrix includes an empty right side, one matching row, duplicate matches, a NULL on the right, and a nullable left key if the schema permits it. Assert exact expected rows, not just row counts. Two incorrect result sets can have the same count.

When this defect appears after a deployment or import, investigate why the missing reference was admitted as well as fixing the report. A query can be logically correct while the upstream data remains incomplete. Reliable anti-joins make the missing-relationship rule explicit, preserve the intended null semantics, and use indexes only after those decisions are settled.

Technical references: Microsoft Learn: IN · Microsoft Learn: EXISTS.