INTERSECT and EXCEPT: Where NULL Finally Behaves

Every earlier part of this series showed NULL producing a result people did not expect. A comparison against NULL is UNKNOWN, and each clause has its own rule for what to do with UNKNOWN: WHERE and a join drop it, a CHECK accepts it, NOT IN inverts it. That is why the same NULL turned up as a dropped row in one place and an accepted row in another. This last part is about the one pair of operators where NULL behaves the way most people assume it should, INTERSECT and EXCEPT, and how to use that to solve the problems from the earlier parts.

This is Part 9 of The UNKNOWN Problem, a series on NULL traps in T-SQL, and the last one. The full index is at the end.

INTERSECT returns the rows that appear in both inputs, and it treats a NULL as equal to a NULL when it compares them.^[1]

Two rows, each with a NULL in the first column and a 1 in the second, and INTERSECT returns one row. The two NULLs were treated as equal. Compare that with the equality operator, where NULL = NULL is UNKNOWN and the rows would not have matched. INTERSECT and EXCEPT do not compare with =. For deciding distinct rows they consider two NULLs equal, the same rule DISTINCT uses and consistent with how GROUP BY groups NULLs, so a NULL matches a NULL and differs from any non-NULL value.

This behavior is what makes the two operators useful for a NULL-safe comparison of whole rows. Part 1 of this series used this pattern to fix a MERGE.^[2] The change-detection predicate d.category_id <> s.category_id skipped a NULL-to-value change, because the comparison was UNKNOWN rather than TRUE. Wrapping the row comparison in NOT EXISTS ( SELECT d... INTERSECT SELECT s... ) fixed it, because INTERSECT compares the two rows with NULLs included and returns a row only when every column matches.

The same pattern gives you a NULL-safe difference. EXCEPT returns the rows in the first input that are not in the second, again treating two NULLs as equal, so EXISTS ( SELECT d... EXCEPT SELECT s... ) is true whenever the two rows differ on any column, a NULL-to-value change included. For the single destination row and single source row in a MERGE predicate, NOT EXISTS ( ... INTERSECT ... ) and EXISTS ( ... EXCEPT ... ) detect the same differences, so use whichever reads better. That equivalence holds for the one-row-versus-one-row comparison here. Across multi-row sets, “no common row” and “a left row missing from the right” are different questions, so do not carry the swap over to set-level queries without checking.^[3]

A few practical limits are worth knowing. INTERSECT and EXCEPT compare every column in the SELECT list and match on the whole row, so you add or remove a column by editing both lists, with no per-column NULL handling to maintain. They return distinct rows, the way UNION without ALL does, and SQL Server has no INTERSECT ALL or EXCEPT ALL, so that duplicate removal is not optional. The distinct behavior is what you want for change detection but is worth remembering if you use these operators elsewhere. Most data types are allowed, including varchar(max), nvarchar(max), and varbinary(max), so a wide text column compares without special handling. The types that cannot take part are xml, text, ntext, image, and the non-binary CLR types such as geometry and geography, which are not comparable and have to be cast or handled separately. And the column count and positions have to line up between the two SELECT lists, with compatible types, the same rule UNION follows.

The reason these operators fix so many of the NULL traps is that their row comparison already treats two NULLs as equal. Everywhere else in the series, a NULL comparison gave you UNKNOWN and you had to add IS NULL legs or a filter to get the behavior you wanted. With INTERSECT and EXCEPT the NULL-safe comparison is built in, as long as the columns are comparable types and you list every column that matters in both projections, so there is less to hand-write and less to get wrong.

The pattern runs through the whole series. A plain comparison against NULL is UNKNOWN, and each clause decides what to do with UNKNOWN by its own rule, so the NULL rows end up somewhere you did not plan unless you say otherwise. In each case, work out how the clause in front of you handles UNKNOWN, and add the explicit NULL handling when that default does not match the result you need.

Here is the full series.

• Part 1, a MERGE change-detection predicate that skips a NULL-to-value change
• Part 2, a NOT IN with a NULL in the list that returns no rows
• Part 3, an inequality filter that drops the NULL rows
• Part 4, a CHECK constraint that accepts the row it looks like it should reject
• Part 5, aggregates that count NULL differently from row to row
• Part 6, a single NULL that turns a whole concatenation into NULL
• Part 7, ISNULL and COALESCE, which are not interchangeable
• Part 8, NULL in unique keys and joins
• Part 9, this post

If you have another NULL trap of your own that belongs in this series, I would like to hear about it. You can find me on Bluesky and LinkedIn.

References

1. EXCEPT and INTERSECT (Transact-SQL) - Microsoft Learn. Documents that INTERSECT and EXCEPT treat two NULLs as equal when comparing rows. ↩
2. Undocumented Query Plans: Equality Comparisons - sql.kiwi, Paul White. Uses NOT EXISTS with INTERSECT for NULL-safe change detection and explains the resulting plan. ↩
3. NULL Complexities, Part 3 - SQLPerformance, Itzik Ben-Gan. Shows EXCEPT and INTERSECT emulating IS [NOT] DISTINCT FROM for NULL-safe comparison. ↩