Does UNION treat two rows containing NULL as duplicates, given NULL = NULL is unknown?

## Two different notions of sameness SQL contains two comparisons that a lot of people assume are one. The first is the `=` operator, evaluated under three-valued logic: if either side is NULL the result is UNKNOWN, and a `WHERE` clause keeps only rows for which the predicate is TRUE. That is why `WHERE city = NULL` returns nothing and why an equality join drops rows whose join key is NULL. The second is the **distinctness** comparison the standard uses for duplicate elimination. Two rows are duplicates when, position by position, neither value is *distinct from* the other — and the standard says two NULLs are not distinct from each other. Under that rule, NULLs match. Every duplicate-removing construct uses the second comparison: `SELECT DISTINCT`, `GROUP BY`, and the set operators `UNION`, `INTERSECT` and `EXCEPT`. So: ```sql SELECT NULL AS v UNION SELECT NULL; -- one row ``` Not two. The predicate spelling of the same comparison is `IS NOT DISTINCT FROM`, so you can think of duplicate elimination as comparing rows with `IS NOT DISTINCT FROM` in every position rather than with `=`. ## What it means for each operator **UNION.** Rows that are NULL in the same positions collapse. Combining a customers list and a suppliers list on a nullable `region` column returns a single NULL-region row, not one per source. **INTERSECT.** A left row with NULL in a column matches a right row with NULL in the same column, so NULL-bearing rows can appear in the intersection. **EXCEPT.** A NULL-bearing row on the right removes the matching NULL-bearing row on the left. This is exactly what you want when reconciling two result sets over nullable columns. ```sql -- a holds one row: (7, NULL) b holds one row: (7, NULL) SELECT id, note FROM a EXCEPT SELECT id, note FROM b; -- zero rows: the NULLs matched ``` Compare that with the equality-join version of the same idea. Joining `a` to `b` on `a.note = b.note` never matches the NULL rows, so a "find the rows that differ" query built from an outer join and an `IS NULL` check reports a spurious difference on every nullable column. The set operator does not have that failure mode, which is a large part of why two-way `EXCEPT` is the standard way to compare result sets. ## Where the rule does *not* apply The NULL-as-equal rule is about **duplicate elimination**, not about predicates. Inside either branch, ordinary three-valued logic still governs: ```sql SELECT id FROM a WHERE note = NULL -- returns nothing, as always UNION SELECT id FROM b; ``` The `UNION` cannot rescue a predicate that filtered the rows away before the set operation ever saw them. Similarly, a `UNIQUE` constraint uses yet another convention — in most engines multiple NULLs are permitted in a unique column precisely because NULLs are treated as distinct there — so "how does this construct treat NULLs" genuinely has to be asked construct by construct. The three regimes to keep straight are: predicates (`NULL = NULL` is UNKNOWN), duplicate elimination and grouping (NULLs are equal), and uniqueness enforcement (engine-specific, commonly NULLs do not conflict). ## Interview-shaped consequences A typical probe: "`SELECT NULL UNION ALL SELECT NULL` versus `SELECT NULL UNION SELECT NULL` — how many rows each?" Two and one. `UNION ALL` removes nothing, so the NULL question never arises; plain `UNION` deduplicates and the two NULLs are duplicates. Another: "Why does `EXCEPT` find no difference between these two tables while my anti-join reports several rows?" Because the anti-join compares with `=`, which never matches NULL to NULL, so every NULL-bearing row looks unmatched. The set operator matched them. A third: "Does the row need to be entirely NULL?" No — matching is per column. `(7, NULL)` and `(7, NULL)` are duplicates; `(7, NULL)` and `(8, NULL)` are not, because the first column differs. ## The practical takeaway When you need NULL-insensitive comparison of whole rows, set operators give it to you for free. When you need it in a predicate, spell it out with `IS NOT DISTINCT FROM` where your engine supports that syntax, or fall back to an explicit `(a = b OR (a IS NULL AND b IS NULL))`. What you must not do is assume a rule you learned in one context carries into the other: SQL deliberately uses different comparisons for filtering and for deduplication, and knowing which is which is what the question is testing.