SQL / JOINS
INTERSECT and EXCEPT for comparing result sets
Use INTERSECT and EXCEPT to compare two result sets row-for-row, and predict how they treat duplicates and NULLs when diffing tables.
What you will learn
- Compare two result sets row by row with INTERSECT and EXCEPT instead of a join
- Predict duplicate collapsing and use INTERSECT ALL / EXCEPT ALL when counts matter
- Use EXCEPT instead of NOT IN when the compared column can hold NULL
- Diff two tables by running EXCEPT in both directions and labelling each side
Understanding INTERSECT and EXCEPT for comparing result sets
A join asks which row over here pairs with which row over there, and answers with columns from both sides glued into one wider row. INTERSECT and EXCEPT ask a different question: given two result sets of the same shape, which whole rows do both produce (INTERSECT), and which whole rows does the left one produce that the right one does not (EXCEPT). Because the comparison is on entire rows, the two SELECTs must return the same number of columns with compatible types, and columns are paired by position, not by name; the output column names come from the first SELECT and the second SELECT's names are ignored. Nothing is widened and nothing is multiplied, so the result can only ever be a subset of what the left query already produced.
By default both operators work on sets, so each input is deduplicated and EXCEPT removes every copy of a matching row rather than one copy per match. The row comparison uses "not distinct" equality instead of =, which is why two rows holding NULL in the same column count as the same row, the exact opposite of how =, a join condition, or NOT IN behave. That difference is the practical reason to prefer EXCEPT over NOT IN on a nullable column: a single NULL on the right side makes NOT IN return nothing at all. When copy counts matter, INTERSECT ALL keeps min(left count, right count) copies of a row and EXCEPT ALL keeps left count minus right count, subtracting duplicates instead of flattening them.
A workable mental model is that EXCEPT is a whole-row anti-join followed by DISTINCT, and INTERSECT is a whole-row semi-join followed by DISTINCT. That makes them the shortest way to check whether two tables agree, but they are directional: an empty result from a EXCEPT b says nothing about rows that exist only in b, so an honest comparison runs both directions. Only one ORDER BY is permitted and it goes after the last SELECT, where it applies to the combined result; without it the order is unspecified, because the engine is free to sort or hash the inputs in whatever way suits its execution plan.
placeholder
CREATE TABLE web_signup (email TEXT);
CREATE TABLE newsletter (email TEXT);
INSERT INTO web_signup VALUES ('ana@ex.com'), ('bo@ex.com'), ('cy@ex.com'), ('ana@ex.com');
INSERT INTO newsletter VALUES ('bo@ex.com'), ('cy@ex.com'), ('dee@ex.com');
-- addresses present on both lists
SELECT email FROM web_signup
INTERSECT
SELECT email FROM newsletter
ORDER BY email;
-- signed up on the site, missing from the newsletter
SELECT email FROM web_signup
EXCEPT
SELECT email FROM newsletter
ORDER BY email;INTERSECT and EXCEPT compare whole rows positionally between two same-shaped result sets, treating NULL as equal to NULL and collapsing duplicates unless you write ALL.
Worked examples
EXCEPT versus NOT IN with NULL present
Shows that EXCEPT matches NULL to NULL while NOT IN collapses to no rows at all.
CREATE TABLE q1_orders (coupon TEXT);
CREATE TABLE q2_orders (coupon TEXT);
INSERT INTO q1_orders VALUES ('SAVE10'), ('FREESHIP'), (NULL);
INSERT INTO q2_orders VALUES ('FREESHIP'), (NULL);
SELECT coupon FROM q1_orders
EXCEPT
SELECT coupon FROM q2_orders;
SELECT coupon FROM q1_orders
WHERE coupon NOT IN (SELECT coupon FROM q2_orders);Example explained
Line 1SAVE10 is the only row of q1_orders with no counterpart in q2_orders, so EXCEPT returns it.
Line 2The NULL row disappears from the EXCEPT result because set operators treat two NULLs as the same row, and q2_orders has one.
Line 3NOT IN evaluates coupon <> NULL for every candidate, which is unknown rather than true, so the predicate can never be satisfied and the second query returns nothing.
Line 4The two queries answer the same business question and disagree only because of NULL, which is why EXCEPT is the safer reconciliation tool here.
Keeping duplicate counts with ALL
Demonstrates the multiset arithmetic behind INTERSECT ALL and EXCEPT ALL.
CREATE TABLE cart_a (item TEXT);
CREATE TABLE cart_b (item TEXT);
INSERT INTO cart_a VALUES ('nail'), ('nail'), ('nail'), ('screw');
INSERT INTO cart_b VALUES ('nail'), ('nail'), ('bolt');
SELECT item FROM cart_a
INTERSECT ALL
SELECT item FROM cart_b
ORDER BY item;
SELECT item FROM cart_a
EXCEPT ALL
SELECT item FROM cart_b
ORDER BY item;Example explained
Line 1INTERSECT ALL keeps min(3, 2) = 2 copies of nail; plain INTERSECT would report a single nail row.
Line 2EXCEPT ALL keeps 3 - 2 = 1 copy of nail, plus screw, which has no match on the right at all.
Line 3bolt never appears in either result: rows that exist only on the right side are outside what both operators can return.
Line 4The trailing ORDER BY sorts the finished set-operation result, not just the second SELECT.
Two-way diff of multi-column rows
Compares (sku, qty) pairs in both directions and labels which side each difference came from.
CREATE TABLE expected (sku TEXT, qty INT);
CREATE TABLE actual (sku TEXT, qty INT);
INSERT INTO expected VALUES ('A1', 10), ('B2', 5), ('C3', 7);
INSERT INTO actual VALUES ('A1', 10), ('B2', 4), ('D4', 2);
SELECT 'missing' AS side, sku, qty
FROM (SELECT sku, qty FROM expected
EXCEPT
SELECT sku, qty FROM actual) AS d
UNION ALL
SELECT 'extra' AS side, sku, qty
FROM (SELECT sku, qty FROM actual
EXCEPT
SELECT sku, qty FROM expected) AS d
ORDER BY side, sku;Example explained
Line 1Each EXCEPT compares the pair (sku, qty) as one unit, so B2 appears on both sides: the sku exists in both tables but its quantity changed.
Line 2The row ('A1', 10) shows up nowhere, because identical rows cancel out in both directions.
Line 3Each EXCEPT is wrapped in a derived table with an alias so the outer SELECT can tag it with a literal side value.
Line 4The single ORDER BY at the very end sorts the whole UNION ALL, which is why the two labelled blocks come out grouped.
Important notes
Dialect check before you ship: Oracle spells EXCEPT as MINUS, MySQL only gained INTERSECT and EXCEPT in 8.0.31, and SQL Server and SQLite support the plain forms but not INTERSECT ALL or EXCEPT ALL.
Parenthesise when mixing operators: the standard gives INTERSECT higher precedence than UNION and EXCEPT, so a UNION b INTERSECT c means a UNION (b INTERSECT c), and not every engine applies that rule the same way.
Common mistakes
Assuming columns pair up by name: SELECT city, name EXCEPT SELECT name, city is perfectly legal when both are text, and the query silently compares city against name, usually reporting every left row as a difference.
Reconciling load counts with plain EXCEPT: if the left table has three copies of a row and the right has one, EXCEPT reports zero differences and the duplicated rows go unnoticed; EXCEPT ALL is the form that surfaces them.
Reading an empty a EXCEPT b as proof the tables match: it only shows a's distinct rows are a subset of b's, so rows that exist only in b stay invisible until you run the reverse direction.
Try it yourself
Change, predict, then run
Create two small tables of tag names where one side deliberately stores the same tag twice, then write EXCEPT in both directions to list the tags unique to each side. Change the direction holding the duplicate to EXCEPT ALL and explain the change in row count.
Open the SQL workspaceCheck your understanding
A nightly check runs SELECT * FROM staging EXCEPT SELECT * FROM warehouse and it returns zero rows. What can you safely conclude?
- The two tables are identical.
- The two tables contain the same number of rows.
- Every distinct row of staging also exists in warehouse, but warehouse may hold extra rows and the copy counts may differ.
- Nothing, because EXCEPT skips any row that contains a NULL.
Show answer
EXCEPT is directional and set-based, so an empty result only proves staging's distinct rows are a subset of warehouse's rows. Concluding the tables are identical is the tempting error: a row present only in warehouse can never appear in that direction, and three copies in staging against one in warehouse also cancel out, so identity needs the reverse EXCEPT and the ALL form. The last option is wrong because rows containing NULL are compared, with NULL matching NULL.