What do INTERSECT and EXCEPT (or MINUS) do?

INTERSECT and EXCEPT are the other two set operators alongside UNION. Both share the same column-count and type requirements.

-- INTERSECT: rows present in BOTH result sets
SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;
-- customers who ordered in both years

-- EXCEPT (Oracle: MINUS): rows in the first set but NOT the second
SELECT customer_id FROM orders_2023
EXCEPT
SELECT customer_id FROM orders_2024;
-- customers who ordered in 2023 but churned before 2024

Equivalent using joins/EXISTS

These set operators are often more readable than the join-based equivalent, but the optimizer typically rewrites them into a semi-join or anti-join internally anyway:

-- Equivalent to the INTERSECT above
SELECT DISTINCT o23.customer_id
FROM orders_2023 o23
WHERE EXISTS (SELECT 1 FROM orders_2024 o24 WHERE o24.customer_id = o23.customer_id);

-- Equivalent to the EXCEPT above
SELECT DISTINCT o23.customer_id
FROM orders_2023 o23
WHERE NOT EXISTS (SELECT 1 FROM orders_2024 o24 WHERE o24.customer_id = o23.customer_id);

Support across engines

• PostgreSQL, SQL Server, SQLite: INTERSECT and EXCEPT
• Oracle: INTERSECT and MINUS (not EXCEPT)
• MySQL: added INTERSECT and EXCEPT in 8.0.31+; earlier versions need a rewrite as JOIN/EXISTS

Both operators deduplicate by default. Like UNION, most engines also support an ALL variant (INTERSECT ALL, EXCEPT ALL) that preserves duplicate counts using multiset semantics, though these are used far less often.