EXCEPT: Compare Distinct Sets Including NULL

I use EXCEPT when the question concerns distinct rows present on one side but absent on the other. It compares sets, so duplicate multiplicity isn’t retained. Direction and NULL handling both belong in that contract.

Read the left-side difference

The first left input contains one twice, two once and NULL twice. The right input contains two and three. Its expected difference contains one and NULL, with each returned only once.

The known value two disappears because it is present on the right. Three isn’t returned because it wasn’t in the left input. I’d keep both source sets visible while explaining the result. EXCEPT isn’t a general symmetric comparison.

WITH L AS
(
 SELECT CAST(ItemValue AS int) AS ItemValue
 FROM (VALUES (1),(1),(2),(CAST(NULL AS int)),(CAST(NULL AS int))) v(ItemValue)
), R AS
(
 SELECT ItemValue FROM (VALUES (2),(3)) v(ItemValue)
)
SELECT ItemValue FROM L EXCEPT SELECT ItemValue FROM R
ORDER BY ItemValue;

WITH L AS
(
 SELECT CAST(ItemValue AS int) AS ItemValue
 FROM (VALUES (1),(1),(2),(CAST(NULL AS int)),(CAST(NULL AS int))) v(ItemValue)
), R AS
(
 SELECT ItemValue FROM (VALUES (2),(3)) v(ItemValue)
)
SELECT ItemValue FROM R EXCEPT SELECT ItemValue FROM L
ORDER BY ItemValue;

WITH L AS
(
 SELECT CAST(ItemValue AS int) AS ItemValue
 FROM (VALUES (1),(1),(2),(CAST(NULL AS int)),(CAST(NULL AS int))) v(ItemValue)
), R AS
(
 SELECT CAST(ItemValue AS int) AS ItemValue
 FROM (VALUES (2),(3),(CAST(NULL AS int))) v(ItemValue)
)
SELECT ItemValue FROM L EXCEPT SELECT ItemValue FROM R
ORDER BY ItemValue;

Reverse the direction deliberately

The second statement compares right minus left using the same inputs. Its expected result is three. That is different from the first statement, which returned the values unique to the other side.

EXCEPT isn’t symmetric. I’d name the two inputs according to their role in the comparison, such as expected and observed, before choosing direction. Otherwise a correct difference can be described incorrectly as missing records when it actually reports unexpected records.

Keep duplicate reduction in view

The repeated ones in the left input produce one result row. The repeated NULL values also produce one row when they survive the comparison. The operator’s distinct-row contract explains both outcomes.

I wouldn’t use this result alone to verify source multiplicity. Two extra duplicate records can disappear during a set comparison. Exact duplicate counts need a separate multiplicity check. An empty distinct-set difference doesn’t establish complete equivalence.

Read NULL equality under the set rule

The third statement adds NULL to the right input. Its expected difference now contains only one. EXCEPT treats two NULL values as equal when it determines distinct rows.

That rule differs from an ordinary equality predicate’s unknown comparison. I’d state which operation is being used instead of relying on a broad slogan about NULL. The same visible inputs can behave differently under a set comparison and a relational join condition.

Compare the complete projected row

This example projects one int column, so the comparison key is small and explicit. A wider EXCEPT compares the complete projected row, not a key chosen from elsewhere. Column count, order and compatible types must match.

I’d review those projections before interpreting a difference as a changed business record. Including a timestamp or display label can change the comparison question. Selecting fewer fields can also hide a difference that matters to the consumer.

Keep ordering and execution claims separate

Each statement includes ORDER BY for a stable teaching display. The query creates no objects and performs no repair. Its expected outputs retain all returned rows rather than summarizing them with a count.

I’d compare those exact small outputs on the target instance, then review a real workload separately. This example establishes directional distinct-set semantics. It doesn’t promise a faster anti-join, validate every source column, or authorize removing records that appear in the difference.

Name the two sides first, and the difference reads itself.

A set difference is not a duplicate audit, it is a directional comparison of distinct projected rows.

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