Set Operations: UNION, INTERSECT, EXCEPT · SQL & Databases

Set operators stack the results of two queries vertically — combining, intersecting or subtracting whole result sets.

Where a join combines tables side by side (more columns), set operators combine query results top to bottom (more rows). UNION merges two result sets, INTERSECT keeps rows common to both, and EXCEPT (MINUS in Oracle) keeps rows in the first but not the second.

UNION vs UNION ALL

UNION stacks two result sets and removes duplicate rows; UNION ALL keeps every row, including duplicates, and is faster because it skips the de-duplication step. Both require the two queries to have the same number of columns with compatible types.

Combine two result sets

SELECT name FROM employees WHERE dept_id = 10
UNION                                   -- removes duplicates
SELECT name FROM employees WHERE salary > 70000;

-- keep duplicates (faster):
SELECT name FROM employees WHERE dept_id = 10
UNION ALL
SELECT name FROM employees WHERE salary > 70000;

INTERSECT and EXCEPT

-- in both result sets:
SELECT dept_id FROM employees
INTERSECT
SELECT dept_id FROM projects;

-- in the first but not the second:
SELECT dept_id FROM departments
EXCEPT
SELECT dept_id FROM employees;

⚡ The edge

UNION removes duplicates (and sorts to do so); UNION ALL keeps everything and is faster. If you know there are no duplicates, prefer UNION ALL.
All set operators require the queries to return the same number of columns with compatible types, matched by position (not name).

Worked example

List department IDs that have employees AND have projects.

Department IDs with employees come from the employees table; with projects, from the projects table.
You want IDs present in both — that's an INTERSECT of the two SELECTs.
Both queries select the single dept_id column.

Answer: SELECT dept_id FROM employees INTERSECT SELECT dept_id FROM projects;

Worked example

When should you use UNION ALL instead of UNION?

UNION removes duplicates, which requires an extra sort/hash step.
If duplicates are impossible or acceptable, that step is wasted work.
So use UNION ALL for better performance when de-duplication isn't needed.

Answer: Use UNION ALL when duplicates can't occur or don't matter — it skips de-duplication and is faster.

⚠ Watch out

UNION silently removes duplicates — if you needed every row, you wanted UNION ALL.
Column count and types must match across the queries, matched by position.
EXCEPT/INTERSECT aren't in every dialect (MySQL historically lacked them); know your database.