UNION, INTERSECT, EXCEPT

JOINs combine tables side by side — glue columns from A onto matching rows of B. The set operators — UNION, INTERSECT, EXCEPT — combine query results stacked vertically, treating each result as a set of rows and applying the set algebra you know from Venn diagrams. They answer a different family of questions: "everything in either," "only the overlap," "in this but not that."

The three operators

Given two SELECTs that produce the same columns:

SELECT name FROM current_employees
UNION
SELECT name FROM former_employees;      -- everyone who ever worked here (deduplicated)

SELECT product FROM q1_sales
INTERSECT
SELECT product FROM q2_sales;           -- products sold in BOTH quarters

SELECT id FROM all_customers
EXCEPT
SELECT customer_id FROM orders;         -- customers who never ordered

• UNION — rows in either result (the combined set).
• INTERSECT — rows in both results (the overlap).
• EXCEPT — rows in the first but not the second (the difference). (Some databases call it MINUS.)

Each maps to a Venn region: UNION is the whole diagram, INTERSECT the lens in the middle, EXCEPT the left-minus-overlap.

The rules that make them work

Two requirements, both enforced:

1. Same number of columns, in the same order, with compatible types. You're stacking rows, so the shapes must match. Column names come from the first query; the second's are ignored.
2. They deduplicate by default. UNION removes duplicate rows (a row appearing in both inputs shows once) — this is set behavior, and it costs a sort/hash to do. If you want duplicates kept (and the speed of skipping the dedup), use UNION ALL — which is also, honestly, what you want most of the time when you know the inputs don't overlap. INTERSECT and EXCEPT also dedup — and note SQLite has no INTERSECT ALL / EXCEPT ALL (those exist in Postgres, but not in the SQLite this course uses).

EXCEPT vs the alternatives (and why it's often cleanest)

"Customers who haven't ordered" — your exercise — can be written three ways, and comparing them is genuinely instructive:

-- EXCEPT: reads like the question
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

-- NOT IN: intuitive, but the NULL trap (last lessons!) can bite
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- LEFT JOIN … IS NULL: the most performant on large tables, least readable
SELECT c.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;

All three find the same customers. EXCEPT wins on clarity (it says "customers except those who ordered"), sidesteps the NOT IN/NULL landmine entirely, and is the right reach when the question is naturally a set difference. The LEFT JOIN … IS NULL form ("anti-join") is what you'd tune toward on millions of rows. Knowing all three — and why you'd pick each — is exactly the intermediate-SQL judgment this course builds.

Ordering the combined result

ORDER BY applies to the whole combined result and goes at the very end, once:

SELECT name FROM a
UNION
SELECT name FROM b
ORDER BY name;      -- orders the union, not just b

Putting ORDER BY on the individual SELECTs is a syntax error in most engines — the ordering belongs to the final set, not its pieces.

Your exercise: Customers Without Orders

Find customers who haven't placed an order, using EXCEPT, output names sorted alphabetically. The clean solution stacks two SELECTs — all customer names EXCEPT the names that appear via a join to orders — with one ORDER BY at the end. The graded essentials: matching column shape between the two SELECTs (select the same one column — names — from each side), the single trailing ORDER BY, and getting the direction right (customers EXCEPT ordered, not the reverse — EXCEPT is not symmetric, unlike UNION and INTERSECT). One operator, and a question that's awkward with joins becomes a sentence.