Interview Question of the Week #043 – What is the Difference Between EXCEPT operator vs. NOT IN

My old interview answer compared EXCEPT with NOT IN using AdventureWorks product IDs. For that non-NULL key example they can return the same IDs. The interesting follow-up is what happens when NULLs or duplicate rows enter the question.

Question: Do EXCEPT and NOT IN always return the same rows?

Answer: No. EXCEPT returns distinct rows from the left query that are absent from the right. NOT IN is a membership predicate. It preserves duplicate outer rows when they qualify, and a NULL in its right-side list can make an unmatched comparison UNKNOWN, so the row does not pass WHERE.

The following standalone data makes both differences visible:

CREATE TABLE #LeftValues (ValueNumber int NULL);
CREATE TABLE #RightValues (ValueNumber int NULL);
INSERT #LeftValues VALUES (1), (2), (2), (NULL);
INSERT #RightValues VALUES (1), (NULL);

SELECT ValueNumber FROM #LeftValues
EXCEPT
SELECT ValueNumber FROM #RightValues;

SELECT ValueNumber FROM #LeftValues
WHERE ValueNumber NOT IN
      (SELECT ValueNumber FROM #RightValues);

EXCEPT returns the value 2 once. The NOT IN query returns no rows because of the right-side NULL. EXCEPT treats two NULLs as equal for distinct set comparison, so the left NULL is removed by the right NULL. If your requirement is “keep each left row with no match,” a correlated NOT EXISTS often expresses that intent more directly:

SELECT l.ValueNumber
FROM #LeftValues AS l
WHERE NOT EXISTS
      (SELECT 1 FROM #RightValues AS r
       WHERE r.ValueNumber = l.ValueNumber);

That last query preserves both copies of 2 and the left NULL under ordinary equality. Whether NULL should survive is a business rule to decide, not an accident of syntax. The original AdventureWorks examples remain a useful historical case, but two queries that agree on one non-NULL key set do not have identical semantics or guaranteed identical plans.

The original non-NULL product comparison

The original product-key queries are still useful. Here are both forms against the current AdventureWorks sample. The historical plan below came from the older sample, not this current server, and does not prove equivalence for nullable or duplicate inputs:

USE AdventureWorks2025;
GO
SELECT ProductID FROM Production.Product
EXCEPT
SELECT ProductID FROM Production.WorkOrder;

SELECT ProductID FROM Production.Product
WHERE ProductID NOT IN
      (SELECT ProductID FROM Production.WorkOrder);

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.