sqlstreak

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.

Practise it now