SQL UNION, INTERSECT and EXCEPT
Joins put tables side by side. Set operations stack whole query results on top of each other, which is perfect for combining similar lists from different tables.
UNION and UNION ALL
UNION combines two results and removes duplicate rows. UNION ALL keeps every row and is faster, because it skips de-duplication. Both queries need the same number of columns with compatible types; column names come from the first query.
SELECT city FROM customers UNION SELECT city FROM staff ORDER BY city;
Labelling where rows came from
Add a fixed text column to each side so you can still tell the sources apart.
SELECT name, 'customer' AS type FROM customers UNION ALL SELECT name, role FROM staff;
INTERSECT: rows in both
INTERSECT keeps only rows that appear in both results.
SELECT city FROM customers INTERSECT SELECT city FROM staff;
EXCEPT: rows in one but not the other
EXCEPT keeps rows from the first query that don't appear in the second. Order matters: swap the queries and you get a different answer.
SELECT city FROM staff EXCEPT SELECT city FROM customers;
Common mistakes
Using UNION when duplicates are impossible. UNION ALL is faster.
Mismatched column counts or types between the two queries.
Putting ORDER BY on the first query. It belongs once, at the very end.
Questions
Is EXCEPT the same as MINUS?+
Yes, Oracle calls it MINUS. PostgreSQL and SQLite use EXCEPT.
UNION or JOIN?+
Use UNION to stack similar rows from different sources; use JOIN to add columns from related tables.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.