UNION, INTERSECT and EXCEPT

Stack and compare whole result sets instead of joining them side by side.

• Intermediate
• 12 min read
• 3 objectives

Before this lessonLesson 4: Joins

A join widens a result: it adds columns from another table. A set operation lengthens one: it adds rows from another query. When a question sounds like "everyone who is either a customer or an employee", you want a set operation.

UNION stacks two results

Both queries must produce the same number of columns, in the same order, with compatible types. The column names come from the first query.

SELECT name, 'customer' AS kind FROM customers
UNION
SELECT name, 'employee' AS kind FROM employees
ORDER BY name;

name     | kind
---------+---------
Ada      | customer
Ada      | employee
Alan     | employee
Barbara  | employee
Grace    | customer
Grace    | employee
Linus    | customer
Linus    | employee
Margaret | employee

UNION removes duplicates, UNION ALL does not

UNION sorts and de-duplicates the combined result, which costs time. UNION ALL just concatenates. If you know the halves cannot overlap, or you want the repeats, always reach for UNION ALL.

SELECT name FROM customers
UNION
SELECT name FROM employees;

name
---------
Ada
Alan
Barbara
Grace
Linus
Margaret

SELECT name FROM customers
UNION ALL
SELECT name FROM employees;

name
---------
Ada
Linus
Grace
Grace
Ada
Linus
Margaret
Alan
Barbara

Every customer in the lesson database is also an employee, so the first result lists six distinct names and the second lists nine rows — the three overlapping names appear twice.

INTERSECT and EXCEPT compare sets

INTERSECT keeps rows present in both results. EXCEPT keeps rows from the first result that are missing from the second — the SQL way of saying "these but not those".

SELECT name FROM customers
INTERSECT
SELECT name FROM employees;

name
------
Ada
Grace
Linus

SELECT name FROM employees
EXCEPT
SELECT name FROM customers;

name
---------
Alan
Barbara
Margaret

Reverse the two halves and you get nothing back at all, because every customer name also appears in employees. EXCEPT is directional — unlike INTERSECT, swapping the sides changes the answer.

SELECT name FROM customers
EXCEPT
SELECT name FROM employees;

(no rows)

Rules worth remembering

• Column count must match; column names need not.
• A single ORDER BY goes at the very end and sorts the combined result.
• LIMIT also applies to the whole result, not to one half.
• Wrap a half in parentheses if you need to sort or limit it on its own.

SELECT name, age FROM customers WHERE age > 30
UNION ALL
SELECT name, salary FROM employees WHERE department = 'Design'
ORDER BY 2 DESC;

name     | age
---------+-------
Margaret | 140000
Alan     | 110000
Barbara  | 110000
Grace    | 45
Ada      | 36

That last query is legal but nonsense: it stacks ages on top of salaries because the types line up. SQL checks shapes, not meaning — that is your job.

Key takeaways

• UNION stacks rows and removes duplicates; UNION ALL keeps them and is faster.
• INTERSECT = in both. EXCEPT = in the first only.
• Every branch needs the same number of columns, and the first branch names them.
• ORDER BY and LIMIT belong to the combined result.

-- Write your solution here