UNION, INTERSECT & EXCEPT

Reviewed & published by Brayan K

Joins combine tables side by side. Set operators combine results top to bottom — stacking, intersecting, or subtracting whole result sets. They answer questions like "everyone in either list", "people in both lists", and "people in this list but not that one".

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

By the end you'll confidently reach for UNION, UNION ALL, INTERSECT, and EXCEPT, and know the column-alignment rules that make them work.

What You'll Learn

• Stack result sets with UNION (de-duped)
• Keep duplicates (and speed) with UNION ALL
• Find common rows with INTERSECT
• Subtract one set from another with EXCEPT
• Line up columns by count and type
• Sort the combined result with one ORDER BY

Our Sample Tables

Two lists of customers — some shop in both channels.

1. UNION — Stack and De-duplicate

UNION places one result set on top of another and removes duplicate rows, giving a clean combined list. Both queries must return the same number of columns with compatible types.

Merging two address books with UNION is like combining them and deleting any contact that appears twice — you end up with one entry per person.

-- The two tables this lesson uses. Run this block first;
-- every later block queries them.
CREATE TABLE online_customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO online_customers VALUES (1, 'Ava'), (2, 'Ben'), (3, 'Cleo');

CREATE TABLE store_customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
INSERT INTO store_customers VALUES
    (1, 'Ava',  'Leeds'),
    (2, 'Ben',  'Bristol'),
    (3, 'Dan',  'Leeds'),
    (4, 'Eve',  'Cardiff');

-- Ava and Ben shop in BOTH channels, which is what makes the
-- difference between UNION and UNION ALL visible below.

-- UNION stacks two result sets and REMOVES duplicates.
SELECT name FROM online_customers
UNION
SELECT name FROM store_customers;

-- Both SELECTs must return the SAME number of columns, in compatible types.

-- ✅ Expected result:
-- name
-- Ava
-- Ben
-- Cleo
-- Dan
-- Eve

2. UNION ALL — Keep Everything

UNION ALL skips the duplicate-removal step. It's faster and keeps every row — ideal when you know the inputs don't overlap, or when duplicates carry meaning.

-- Uses the two customer tables from the first example.
-- UNION ALL stacks them and KEEPS duplicates (and is faster).
SELECT name FROM online_customers
UNION ALL
SELECT name FROM store_customers;

-- Ava and Ben appear twice — they shop in both channels.
-- Use UNION ALL when you KNOW there are no duplicates, or want them counted.

-- ✅ Expected result:
-- name
-- Ava
-- Ben
-- Cleo
-- Ava
-- Ben
-- Dan
-- Eve

3. INTERSECT — Rows in Both

INTERSECT returns only the rows that appear in both result sets — here, customers who shop both online and in store.

-- Uses the two customer tables from the first example.
-- INTERSECT returns only rows present in BOTH result sets —
-- the customers who shop online AND in store.
SELECT name FROM online_customers
INTERSECT
SELECT name FROM store_customers;

-- Note: MySQL lacks INTERSECT before 8.0.31 — use a JOIN or IN instead.

-- ✅ Expected result:
-- name
-- Ava
-- Ben

4. EXCEPT — Subtract a Set

EXCEPT returns rows in the first query that are not in the second. Order matters — swapping the two SELECTs flips the answer. (Oracle calls this MINUS.)

-- Uses the two customer tables from the first example.
-- EXCEPT returns rows in the FIRST set that are NOT in the second,
-- so this is the online-only customers.
SELECT name FROM online_customers
EXCEPT
SELECT name FROM store_customers;

-- Order matters! Swapping the queries gives store-only customers instead.
-- (Oracle spells EXCEPT as MINUS.)

-- ✅ Expected result:
-- name
-- Cleo

Your Turn: one merged list

Fill in the operator that merges both tables into one de-duplicated list of names.

-- 🎯 YOUR TURN — fill in the blank
-- Uses the two customer tables from the first example.
-- Goal: a single de-duplicated list of every customer name from both tables.

SELECT name FROM online_customers
___                               -- 👉 replace ___ with UNION
SELECT name FROM store_customers;

-- ✅ Expected result:
-- name
-- Ava
-- Ben
-- Cleo
-- Dan
-- Eve

5. Sorting and Labelling Combined Results

A single ORDER BY at the very end sorts the whole combined set. Adding a constant "channel" column is a neat trick to remember which query each row came from.

-- Uses the two customer tables from the first example.
-- ORDER BY goes ONCE, at the very end — it sorts the combined result.
SELECT name, 'online' AS channel FROM online_customers
UNION ALL
SELECT name, 'store'  AS channel FROM store_customers
ORDER BY name;

-- ✅ Expected result:
-- name | channel
-- Ava | online
-- Ava | store
-- Ben | online
-- Ben | store
-- Cleo | online
-- Dan | store
-- Eve | store

6. Column Compatibility Rules

Set operators line columns up by position, not name. Every SELECT must return the same number of columns with compatible types. If counts don't match, pad the shorter side with a placeholder like NULL.

-- Uses the two customer tables from the first example.
-- The columns must LINE UP by position and have compatible types.
--
-- This FAILS — different column counts, so it is left commented out.
-- Uncomment it and the whole block stops with
-- "SELECTs to the left and right of UNION do not have the same number of result columns":
--   SELECT name FROM online_customers
--   UNION
--   SELECT name, city FROM store_customers;   -- ❌ 1 column vs 2

-- This WORKS — pad the shorter side so counts match:
SELECT name, NULL AS city FROM online_customers
UNION
SELECT name, city         FROM store_customers;

-- ✅ Expected result:
-- name | city
-- Ava | NULL
-- Ava | Leeds
-- Ben | NULL
-- Ben | Bristol
-- Cleo | NULL
-- Dan | Leeds
-- Eve | Cardiff

Reorder Challenge

These lines of a sorted UNION query are scrambled. Put them in order:

A.   SELECT name FROM store_customers
B.   ORDER BY name
C.   SELECT name FROM online_customers
D.   UNION

Quick Recall

1) Online = {Ava, Ben, Cleo}, Store = {Ava, Ben, Dan, Eve}. What does UNION return?

Ava, Ben, Cleo, Dan, Eve — five unique names. UNION removes the duplicate Ava and Ben.

2) What does INTERSECT return for those same sets?

Ava and Ben — the only names present in both lists.

3) SELECT ... FROM store EXCEPT SELECT ... FROM online returns?

Dan and Eve — store names that are not online. Order matters: EXCEPT subtracts the second set from the first.

Common Errors (and the fix)

• "each UNION query must have the same number of columns": align the SELECTs; pad with NULL if needed.
• ORDER BY on an intermediate SELECT: put one ORDER BY at the very end instead.
• Unexpected duplicates: you used UNION ALL when you wanted UNION.
• EXCEPT in the wrong order: remember it's "first set minus second"; swap the queries for the other direction.
• INTERSECT/EXCEPT in old MySQL: unsupported before 8.0.31 — use a JOIN or IN/NOT IN.

Mini-Challenge: Store-Only Customers

Pick the right set operator to find customers who only appear in the store list. Write it and verify in a playground.

-- 🎯 MINI-CHALLENGE
-- Using the two customer tables:
--   1. Find customers who are ONLY in the store table (not online)
--   2. Return just their name
--   3. Hint: which set operator returns "in A but not B"?
--
-- ✅ Expected: Dan, Eve

-- your query here

🎉 Lesson Complete

• ✅ UNION stacks results and removes duplicates
• ✅ UNION ALL keeps duplicates and is faster
• ✅ INTERSECT returns rows present in both sets
• ✅ EXCEPT subtracts the second set from the first (order matters)
• ✅ Columns must align by count and type; sort with one final ORDER BY
• ✅ Next: CREATE TABLE & data types — building tables of your own

Practice quiz

Continue this course

• Previous: CAST & CONVERT
• Next: CREATE TABLE & Data Types — Create tables and choose the right column data types
• Quick reference: SQL cheat sheet › Set Operations

Frequently asked questions

What's the difference between UNION and UNION ALL?

UNION removes duplicate rows from the combined result, which requires the engine to sort and compare every row — so it's slower. UNION ALL simply concatenates the two result sets and keeps every duplicate, making it faster. Use UNION ALL whenever you know the inputs don't overlap, or when you actually want the duplicates counted.

Why do my UNION queries need the same number of columns?

A set operation stacks one result on top of another, so the columns must line up by position. Each SELECT must return the same number of columns and the matched columns must have compatible data types. The column names come from the first SELECT; the others just need to align positionally.

Does every database support INTERSECT and EXCEPT?

PostgreSQL, SQL Server, SQLite, and Oracle support them (Oracle calls EXCEPT 'MINUS'). MySQL only added INTERSECT and EXCEPT in version 8.0.31; on older MySQL you emulate them with JOINs or IN/NOT IN subqueries. UNION and UNION ALL are supported everywhere.

Where does ORDER BY go in a UNION query?

Only once, at the very end, after the final SELECT. It sorts the entire combined result, not the individual pieces. You can't put ORDER BY on the intermediate SELECTs (except inside a parenthesized subquery with LIMIT in some engines).

Related lessons

• SELECT statements