AI Hints
Homeโ€บSQL Tutorialโ€บCTEs, EXISTS, and Set Operations
SQL ยท Lesson 61 of 126

UNION, INTERSECT, and EXCEPT

Combining results.

โฑ 2 min readโ–ถ Video at 1:26:39๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

UNION, INTERSECT, and EXCEPT
From the video: 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    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.

query.sqlsql
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'
);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
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'
);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

โœ๏ธ Exercise: Brand new customers

Customers who ordered in 2025 but not in 2024

Show hint

2025 query EXCEPT 2024 query

Show solution
solution.sqlsql
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'
);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

Same numberof columns
Same typesin the same order
Namesfrom the first query
ORDER BYonce, at the 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
Next up: Window Functions โ€” numbering rows without grouping