UNION, INTERSECT, and EXCEPT
Combining results.
Joins put tables side by side. But sometimes you want to stack results on top of each other, or compare two lists. For that, SQL has three set operations. Union, intersect, and except.

- Joins: side by side
- Sets: stacked
- UNION, INTERSECT, EXCEPT
UNION
Union stacks two results, and removes duplicates. Both queries must have the same number of columns. Here, cities in the UK, plus cities in Ireland. Run it. Four cities, in alphabetical order. If you want to keep duplicates, use union all, which is also faster.
SELECT city FROM customers WHERE country = 'UK' AND city IS NOT NULL
UNION
SELECT city FROM customers WHERE country = 'Ireland' AND city IS NOT NULL
ORDER BY city;โโโโโโโโโโโโโโ โ city โ โโโโโโโโโโโโโโค โ Dublin โ โ Leeds โ โ London โ โ Manchester โ โโโโโโโโโโโโโโ
INTERSECT
Intersect keeps only rows that appear in both results. Which customers ordered in twenty twenty four, and again in twenty twenty five? Run it. Two hundred and twenty three loyal customers came back.
SELECT COUNT(*) AS bought_both_years
FROM (
SELECT customer_id FROM orders WHERE order_date < '2025-01-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2025-01-01'
);โโโโโโโโโโโโโโโโโโโโโ โ bought_both_years โ โโโโโโโโโโโโโโโโโโโโโค โ 223 โ โโโโโโโโโโโโโโโโโโโโโ
EXCEPT
Except keeps rows from the first result, that are not in the second. Which customers ordered in twenty twenty four, but not in twenty twenty five? Run it. Forty eight customers didn't come back. Helen might send them a we miss you email.
SELECT COUNT(*) AS did_not_come_back
FROM (
SELECT customer_id FROM orders WHERE order_date < '2025-01-01'
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2025-01-01'
);โโโโโโโโโโโโโโโโโโโโโ โ did_not_come_back โ โโโโโโโโโโโโโโโโโโโโโค โ 48 โ โโโโโโโโโโโโโโโโโโโโโ
Brand new customers
Your turn. How many customers ordered in twenty twenty five, but never in twenty twenty four? These are Helen's brand new buyers. Pause and try. Here's a solution. Swap the two queries around the except. One hundred and ninety two new buyers in twenty twenty five.
Customers who ordered in 2025 but not in 2024
Show hint
2025 query EXCEPT 2024 query
Show solution
SELECT COUNT(*) AS new_in_2025
FROM (
SELECT customer_id FROM orders WHERE order_date >= '2025-01-01'
EXCEPT
SELECT customer_id FROM orders WHERE order_date < '2025-01-01'
);โโโโโโโโโโโโโโโ โ new_in_2025 โ โโโโโโโโโโโโโโโค โ 192 โ โโโโโโโโโโโโโโโ
Rules for set operations
A few rules for union, intersect, and except. Both queries must have the same number of columns. The columns should hold the same kind of data, in the same order. The column names come from the first query. And order by goes once, at the very end.
Summary
Union stacks, intersect keeps what's in both, and except keeps what's only in the first. Next, one of the most loved features in modern SQL. Window functions.
Key takeaways- UNION stacks
- INTERSECT: in both
- EXCEPT: first only