EXCEPT and INTERSECT in SQL Explained

UNION gets all the attention, but it has two siblings that complete SQL's set-operation family: INTERSECT returns rows that appear in both queries, and EXCEPT returns rows in the first query that don't appear in the second. Together they let you express "in both", "in A but not B", and "in either" without writing a single join.

What do EXCEPT and INTERSECT do?

Both operators sit between two complete SELECT statements, exactly like UNION. INTERSECT keeps a row only if it appears in both result sets; EXCEPT keeps a row only if it appears in the first result set and not the second. Order matters for EXCEPT — A EXCEPT B and B EXCEPT A answer different questions.

-- Customers who ordered in BOTH years
SELECT customer_id FROM orders_2025
INTERSECT
SELECT customer_id FROM orders_2026;

-- Customers who ordered in 2025 but NOT in 2026 (churn candidates)
SELECT customer_id FROM orders_2025
EXCEPT
SELECT customer_id FROM orders_2026;

The column rules are the same as UNION's: same column count, compatible types, and comparison happens across the whole row. If you only remember UNION from this family, our UNION vs UNION ALL guide covers the third sibling.

Do EXCEPT and INTERSECT remove duplicates?

Yes — by default both behave like UNION (not UNION ALL): the output is a proper set with duplicates collapsed. The standard also defines INTERSECT ALL and EXCEPT ALL, which keep duplicate multiplicity using a "bag difference" rule, but support is spotty: PostgreSQL implements both, SQL Server implements neither.

-- table a: (1), (1), (2)      table b: (1)

SELECT n FROM a EXCEPT     SELECT n FROM b;  -- returns: 2
SELECT n FROM a EXCEPT ALL SELECT n FROM b;  -- returns: 1, 2
-- EXCEPT ALL subtracts occurrences: two 1s minus one 1 leaves one 1

A pleasant surprise for anyone burned by NULL comparisons: set operators treat NULLs as equal to each other. (NULL, 'x') in both inputs counts as a match for INTERSECT — unlike a join predicate, where NULL = NULL is unknown and the rows would not match.

How do I write EXCEPT and INTERSECT in MySQL?

MySQL 8.0.31 (October 2022) finally added both operators, so on a current MySQL the standard syntax just works. On anything older — and on MariaDB before 10.3 — you emulate them with NOT EXISTS / EXISTS correlated subqueries, or IN / NOT IN if the compared column is NOT NULL.

-- EXCEPT emulation (works on any MySQL version)
SELECT DISTINCT o25.customer_id
FROM orders_2025 o25
WHERE NOT EXISTS (
    SELECT 1 FROM orders_2026 o26
    WHERE o26.customer_id = o25.customer_id
);

-- INTERSECT emulation
SELECT DISTINCT o25.customer_id
FROM orders_2025 o25
WHERE EXISTS (
    SELECT 1 FROM orders_2026 o26
    WHERE o26.customer_id = o25.customer_id
);

Avoid NOT IN when the subquery column can contain NULL: a single NULL makes NOT IN return no rows at all, silently. NOT EXISTS has no such trap, which is why it's the recommended emulation.

Is Oracle MINUS the same as EXCEPT?

Yes. Oracle historically used the keyword MINUS for exactly the standard's EXCEPT semantics — first-query rows not present in the second, duplicates removed. Oracle 21c added EXCEPT as a synonym, so new code can use the standard keyword; older Oracle requires MINUS.

-- Oracle (all versions)
SELECT customer_id FROM orders_2025
MINUS
SELECT customer_id FROM orders_2026;

-- Oracle 21c+ also accepts:
SELECT customer_id FROM orders_2025
EXCEPT
SELECT customer_id FROM orders_2026;

When should I use EXCEPT instead of a join?

EXCEPT shines for whole-row comparisons — especially validating that two tables are identical after a migration, where writing a join predicate over 30 columns (with NULL-safe equality for each) would be miserable. Because set operators compare full rows and treat NULLs as equal, a two-line diff catches every discrepancy.

-- Migration check: rows that differ in ANY column
SELECT * FROM customers_old
EXCEPT
SELECT * FROM customers_new;   -- rows lost or changed

SELECT * FROM customers_new
EXCEPT
SELECT * FROM customers_old;   -- rows added or changed

-- Both empty => tables are identical

For single-key membership questions on large tables, an anti-join (NOT EXISTS) sometimes optimizes better and lets you select columns from the outer table freely — set operators force both sides to the same column list. Measure both if performance matters.