EXCEPT
Return the rows in the first query block that are not in the second — the set-operator form of an anti-join, from MySQL 8.0.31.
SELECT ... EXCEPT [ALL | DISTINCT] SELECT ...EXCEPT limits the result of the first query block to the rows that do not appear in the second. It is the set-operator spelling of an anti-join, and the natural tool for diffing and reconciliation: what is in staging that never reached the warehouse, what the old query returns that the new one does not.
Version
EXCEPT arrived with INTERSECT in MySQL 8.0.31 (October 2022) and is present in MySQL 8.4 LTS. Older releases reject the keyword, and the equivalent LEFT JOIN or NOT EXISTS form is what you will find in code written before then.
Order matters
Unlike UNION and INTERSECT, EXCEPT is not commutative. a EXCEPT b and b EXCEPT a answer different questions, and a full diff needs both halves.
DISTINCT and ALL
EXCEPT is shorthand for EXCEPT DISTINCT: duplicates are removed from either side, so a row that appears twice on the left and nowhere on the right comes back once. EXCEPT ALL retains duplicates instead — the same row appearing twice on the left comes back twice.
The rows are compared whole, column by column, by position. Two NULLs in the same position are treated as equal for this purpose, which is exactly the trap that makes the older NOT IN rewrite behave differently.
Example
-- Rows that landed in staging but never made it into the warehouse.
-- Requires MySQL 8.0.31 or later; present in 8.4 LTS.
SELECT order_id, sku, qty FROM staging_orders
EXCEPT
SELECT order_id, sku, qty FROM warehouse_orders;
-- The same answer as an anti-join, which is how it was written before 8.0.31.
-- The LEFT JOIN keeps every staging row; IS NULL on a NOT NULL right-hand
-- column then keeps only the ones that found no match.
SELECT DISTINCT s.order_id, s.sku, s.qty
FROM staging_orders AS s
LEFT JOIN warehouse_orders AS w
ON w.order_id = s.order_id
AND w.sku = s.sku
AND w.qty = s.qty
WHERE w.order_id IS NULL;When to use it
- List rows that exist in staging but never reached the warehouse.
- Find customers, accounts or SKUs with no matching row in a related table.
- Diff the output of an old query against a rewritten one during a migration.
- Produce a two-sided reconciliation report by unioning both directions of EXCEPT.
More examples
EXCEPT, NOT EXISTS and the NOT IN trap
The first two agree in all cases. The third is the rewrite people reach for, and it silently returns an empty result as soon as a NULL appears in the subquery column — a bug that only shows up once real data arrives. EXCEPT and NOT EXISTS both sidestep it.
-- Customers who have never placed an order.
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;
-- Equivalent, and safe on every MySQL 8 release.
SELECT c.id
FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);
-- NOT the same thing. If orders.customer_id contains even one NULL, the
-- NOT IN predicate is UNKNOWN for every row and the query returns nothing.
SELECT c.id
FROM customers AS c
WHERE c.id NOT IN (SELECT customer_id FROM orders);A two-sided diff
The standard symmetric-difference report, useful after a migration or an ETL run. The literal label has to appear in both blocks of each EXCEPT, otherwise the rows being compared would differ in their first column and nothing would ever match. UNION ALL is correct at the top level because the two halves cannot overlap.
-- Everything that differs between the two tables, labelled by which side
-- it came from. EXCEPT is directional, so both halves are needed.
(SELECT 'staging only' AS side, order_id, sku, qty
FROM staging_orders
EXCEPT
SELECT 'staging only', order_id, sku, qty
FROM warehouse_orders)
UNION ALL
(SELECT 'warehouse only', order_id, sku, qty
FROM warehouse_orders
EXCEPT
SELECT 'warehouse only', order_id, sku, qty
FROM staging_orders)
ORDER BY side, order_id;Keeping duplicate rows with ALL
Reach for ALL when the number of copies of a row is itself the data — expected versus received payments, stock movements, event counts. With the default DISTINCT, a row is either in the result or not, and multiplicity is thrown away before the comparison.
-- Given payments_expected holding (ref 'A', 100) twice and payments_received
-- holding it once:
SELECT ref, amount_cents FROM payments_expected
EXCEPT
SELECT ref, amount_cents FROM payments_received;
-- DISTINCT is the default, so ('A', 100) is compared as a set membership
-- question: it exists on the right, so nothing comes back for it.
SELECT ref, amount_cents FROM payments_expected
EXCEPT ALL
SELECT ref, amount_cents FROM payments_received;
-- ALL retains duplicates on the left rather than collapsing them first.
Discussion