IntermediateLesson 15 of 305 min read

Set Operations: UNION, INTERSECT and EXCEPT

Stack, overlap and subtract query results with UNION, UNION ALL, INTERSECT and EXCEPT, and avoid losing rows to UNION.

In this lesson you will

  • Combine results with UNION and UNION ALL
  • Find overlap with INTERSECT
  • Subtract with EXCEPT
  • Follow the column rules

About 25 minutes

Stacking one result on another

So far, queries have combined tables sideways, with joins. Sometimes you need to stack results on top of each other, as when you combine two lists. Which customers ordered early in the summer or late in the autumn? Which are in both of two groups? Which are in one group and not the other? SQL has three set operations for exactly this: UNION, INTERSECT and EXCEPT.

The operations

  • UNION: all the rows from both queries, with repeated rows removed.
  • UNION ALL: all the rows from both, repeats kept. It is quicker, because the database does not have to look for duplicates.
  • INTERSECT: only rows that appear in both results.
  • EXCEPT: rows from the first result that do not appear in the second. Order matters here. Some databases, such as Oracle, call it MINUS.

There are rules. Both queries must return the same number of columns, with compatible types, in the same order. The column names of the result come from the first query. And an ORDER BY at the very end applies to the combined result, not to either half.

Five set queries

-- 1
SELECT customer_id FROM orders WHERE order_date < '2026-07-01'
UNION SELECT customer_id FROM orders WHERE order_date >= '2026-10-01' ORDER BY 1

-- 2
SELECT
 (SELECT COUNT(*) FROM (SELECT customer_id FROM orders WHERE status = 'shipped'
   UNION ALL SELECT customer_id FROM orders WHERE status = 'pending')) AS with_duplicates,
 (SELECT COUNT(*) FROM (SELECT customer_id FROM orders WHERE status = 'shipped'
   UNION SELECT customer_id FROM orders WHERE status = 'pending')) AS without_duplicates

-- 3
SELECT customer_id FROM orders WHERE status = 'shipped'
INTERSECT SELECT customer_id FROM orders WHERE order_date >= '2026-09-01' ORDER BY 1

-- 4
SELECT customer_id FROM orders
EXCEPT SELECT customer_id FROM orders WHERE status = 'shipped' ORDER BY 1

-- 5
SELECT name AS label, 'customer' AS kind FROM customers WHERE city = 'York'
UNION ALL SELECT name, 'product' FROM products WHERE category = 'pot' ORDER BY kind, label
-- 1
customer_id
1
2
3
4
-- 2
with_duplicates | without_duplicates
9 | 5
-- 3
customer_id
1
4
-- 4
customer_id
3
5
-- 5
label | kind
Ben | customer
Ella | customer
Clay pot | product
Glass pot | product

Query 1 combines customers who ordered before July with those who ordered from October, and gets customers 1, 2, 3, 4. Query 2 shows the effect of duplicates. Stacking the shipped and the pending order owners with UNION ALL gives 9 rows, one for every order. Using UNION instead gives 5, because each customer is counted once, however many orders they have. Query 3 uses INTERSECT: customers with a shipped order who also ordered from September onwards are customers 1, 4. Query 4 uses EXCEPT to find customers who ordered but never had a shipped order: customers 3, 5. Query 5 shows that set operations are not limited to the same table. It stacks York customers and pot products into one list with a label column saying which is which, and the sort puts customers first: Ben, Ella, Clay pot, Glass pot.

Sets as a quick audit

EXCEPT has a second life as a checking tool. Suppose you copy customers into a new table, or import a file, and want to know whether anything went missing. Run EXCEPT in both directions: rows in the old list that are not in the new one, and rows in the new list that are not in the old one. If both come back empty, the two lists match. If not, the rows that appear are exactly the ones to investigate. It is a short, honest test, and it works without writing a join condition for every column. Remember that two NULLs count as equal here, which is helpful for this job, and that you should compare the same columns in the same order.

Choosing the right operation

  • Use UNION ALL when the two sets cannot overlap, or when you want every row, such as stacking this month’s sales on last month’s.
  • Use UNION when you want a clean list of distinct values from two sources.
  • Use INTERSECT for ‘in both’ questions. A join or an EXISTS test can do the same job.
  • Use EXCEPT for ‘in this but not that’. NOT EXISTS is the alternative, and it is the better one when columns might contain NULLs.

Set operations also treat NULLs differently from comparisons: two NULLs count as the same value when removing duplicates. That is useful, but it differs from the usual rule that a NULL never equals anything. Support for INTERSECT and EXCEPT, and for the ALL forms, varies between databases and versions, so check what yours offers.

Common mistake: UNION where UNION ALL was meant

Using UNION when you meant UNION ALL, and silently losing rows. A UNION removes repeats, so a total built on it can be too small. Use UNION ALL when every row counts.

Made-up sample data

The customers and products are made up. UNION, INTERSECT and EXCEPT are standard, but not every database supports all of them in every version, and Oracle uses MINUS for EXCEPT. Check your documentation.

Write a query for customers who ordered in October but not in June, using EXCEPT. Then write the same question with NOT EXISTS and compare.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What is the difference between UNION and UNION ALL?

  1. UNION is faster
  2. UNION ALL removes repeats
  3. UNION removes repeated rows; UNION ALL keeps them
  4. There is none

The two queries gave 5 and 9 rows.

What does INTERSECT return?

  1. Rows that appear in both results
  2. Rows from the first only
  3. All rows from both
  4. Rows in neither

Customers 1 and 4 were in both groups.

What does EXCEPT return?

  1. Rows in both
  2. Rows in the first result that are not in the second
  3. Rows in the second only
  4. A count

Order matters: swap the queries and the answer changes.

What must two queries have in common to be combined?

  1. The same table
  2. The same WHERE clause
  3. The same ORDER BY
  4. The same number of compatible columns

The columns must line up in number and type.

Where does ORDER BY go in a combined query?

  1. After each query
  2. At the very end, applying to the whole result
  3. Before the first SELECT
  4. Inside a bracket only

One ORDER BY sorts the combined result.

Summary

  • UNION removes duplicates; UNION ALL keeps them.
  • INTERSECT finds overlap; EXCEPT subtracts.
  • Both queries need matching columns.
  • Support differs between databases.

Next lesson

Lesson 16 introduces window functions, starting with ranking.

Questions

Is UNION ALL always better?

It is faster and keeps every row, so use it when you want every row or when overlap is impossible. Use UNION when you need distinct values.

Is EXCEPT the same everywhere?

The idea is, but Oracle calls it MINUS and support varies between databases and versions. Check yours.

Can I combine more than two queries?

Yes. Chain more operations. Use brackets where the order matters, if your database supports them.

Progress is saved in this browser.