CSE Roadmap

Common tier · about 3 hours · 5 parts

Part 4 of 5 · 1 min read

EXCEPT

Keeping rows from the first query's result that are absent from the second.

EXCEPT

Think of comparing two guest lists and keeping only the names on the first list that never appear on the second.

In plain words: EXCEPT returns rows present in the first query's result but absent from the second. Order matters: swapping the two queries changes the result, unlike UNION and INTERSECT, where the two sides are interchangeable.

A worked example. Customers who ordered in January but not in February.

The key lines.

SELECT customer_id FROM orders WHERE placed_on BETWEEN '2026-01-01' AND '2026-01-31'
EXCEPT
SELECT customer_id FROM orders WHERE placed_on BETWEEN '2026-02-01' AND '2026-02-28';

EXCEPT

EXCEPT returns rows present in the first query's result but absent from the second. The order of the two queries changes the result.

Example: swapping the two queries above would instead return customers who ordered in February but not January.

Remember this

UNION ALL is always correct and usually faster than UNION whenever duplicates are known not to matter, or known not to be possible, since skipping the duplicate check costs nothing extra to verify and saves real work.

Quick notes

  • UNION removes duplicates; UNION ALL keeps them, and is faster when duplicates don't matter.
  • INTERSECT keeps only rows present in both queries.
  • EXCEPT keeps rows from the first query absent from the second; the order of the two queries matters.
  • Every set operation requires both queries to return the same number of columns, in compatible types.
  • UNION, UNION ALL and INTERSECT are symmetric between their two sides; EXCEPT is not.