Reference

SQL Set Operations

How to stack, deduplicate, overlap, and subtract compatible SELECT results.

Set operations combine whole result sets. Instead of joining columns side by side, they stack or compare rows from separate SELECT statements.

Basic shape

select column_one, column_two
from first_table
union all
select column_one, column_two
from second_table
order by column_one

Practice

Practice UNION Practice UNION ALL Practice INTERSECT Practice EXCEPT

Compatibility rules

Every SELECT in a set operation must return the same number of columns. Matching columns should have compatible types, and the output column names come from the first SELECT.

UNION ALL and UNION

UNION ALL stacks rows and keeps every row. UNION stacks rows and removes duplicate rows from the combined result. Prefer UNION ALL unless deduplication is part of the question.

Distinct values from two sources

select state
from customers
union
select state
from suppliers
order by state

INTERSECT

INTERSECT returns rows that appear in both result sets. It is useful for finding overlap, such as values shared by two groups.

Shared values

select state
from customers
intersect
select state
from suppliers

EXCEPT

EXCEPT returns rows from the first result set after removing rows that appear in the second result set. It is useful for missing-list questions.

Rows in one result but not another

select customer_id
from customers
except
select customer_id
from orders

Operator	What it does	Duplicate behavior
`UNION ALL`	Stacks all rows.	Keeps duplicates.
`UNION`	Stacks rows.	Removes duplicate rows.
`INTERSECT`	Keeps only shared rows.	Returns distinct shared rows.
`EXCEPT`	Subtracts the second result.	Returns distinct remaining rows.