What does EXCEPT return, and how does it treat duplicate rows?

## What EXCEPT computes `EXCEPT` is SQL's set-difference operator. Written `A EXCEPT B`, it returns the rows produced by query A that do not appear in the result of query B. Like the other set operators it works on whole rows, matched by column position, and it requires the two queries to be compatible: same number of columns, per-pair compatible types. ```sql SELECT customer_id FROM customers EXCEPT SELECT customer_id FROM orders; -- customers who have never ordered ``` Oracle spells the same operator `MINUS`; the semantics are the difference operator described here. ## Duplicates: the DISTINCT default The standard defines `EXCEPT [ALL | DISTINCT]`, and bare `EXCEPT` means `EXCEPT DISTINCT`. Concretely, given left-hand values 1, 1, 2, 3 and a right-hand value 2: ```sql -- left holds 1, 1, 2, 3 ; right holds 2 SELECT x FROM left_side EXCEPT SELECT x FROM right_side; -- returns two rows: 1 and 3 ``` The 2 is removed because it appears on the right; the two 1s collapse to a single row because the operator deduplicates. Candidates frequently predict three rows here — that is the mistake to avoid. `EXCEPT ALL` keeps multiplicities instead, using multiset arithmetic: a row occurring *m* times on the left and *n* times on the right appears max(m − n, 0) times in the result. With left = {1, 1, 2, 3} and right = {2} that returns 1, 1 and 3 — three rows. The mirror rule holds for `INTERSECT ALL`, which returns each row min(m, n) times. Be careful with the `ALL` forms in portable code. `UNION ALL` is universal, but the `ALL` variants of `INTERSECT` and `EXCEPT` are much less consistently implemented across engines — some support them, some support only the `DISTINCT` behaviour, and some do not have `INTERSECT`/`EXCEPT` at all in older versions. Check your engine's documentation before relying on `EXCEPT ALL`. ## INTERSECT, the sibling `A INTERSECT B` returns the rows present in **both** results, again distinct by default. With left = {1, 1, 2} and right = {1, 2, 2}, `INTERSECT` returns two rows, 1 and 2 — the multiplicities on each side are irrelevant to the default form. Unlike `EXCEPT`, `INTERSECT` is commutative: `A INTERSECT B` and `B INTERSECT A` produce the same set of rows (row *order* is undefined for both anyway). That asymmetry between the two operators is a favourite interview probe. ## Not commutative, and why that is useful Because `A EXCEPT B` and `B EXCEPT A` answer different questions, the pair of them is the standard way to prove two result sets are identical: run the difference in both directions and check that both come back empty. One direction alone tells you only that nothing is *missing*, not that nothing is *extra*. This is the workhorse check when validating a rewritten query, a data migration, or a refactored view against the original. ```sql (SELECT id, status FROM v_orders_old EXCEPT SELECT id, status FROM v_orders_new) UNION ALL (SELECT id, status FROM v_orders_new EXCEPT SELECT id, status FROM v_orders_old); -- zero rows means the two views agree ``` The deduplication default has a sting in that use: the two-way difference proves the *sets* agree, not that the row counts agree. If one side has a duplicate row the other lacks, both differences are still empty. When multiplicity is part of what you are validating, compare counts as well, or use the `ALL` forms if your engine has them. ## NULLs Duplicate matching in set operators treats NULLs as equal, so a row whose value is NULL on the left is removed by a row whose value is NULL on the right — even though `NULL = NULL` evaluates to UNKNOWN in a `WHERE` clause. That makes `EXCEPT` NULL-safe in a way that a naive value comparison is not, and it is the main reason `EXCEPT` behaves predictably in reconciliation queries where nullable columns are involved. ## Common misreadings - **"EXCEPT removes rows one for one."** Only `EXCEPT ALL` does that arithmetic; plain `EXCEPT` produces a distinct set. - **"Order of the operands doesn't matter."** It absolutely does for `EXCEPT`; it does not for `INTERSECT` or `UNION`. - **"The result keeps the left query's ordering."** No set operator promises any row order. Add an `ORDER BY` at the end of the whole statement if you need one. - **"EXCEPT compares only the first column."** It compares the entire output row, so a stray extra column in the select list can make every left row survive.