UNION, UNION ALL, INTERSECT and EXCEPT

Set operators stack the results of two queries. UNION ALL keeps every row, and UNION removes duplicates. INTERSECT keeps rows found in both, and EXCEPT keeps rows in the first but not the second.

Why it matters

Sometimes the rows you need come from two places: every city mentioned by departments or customers; cities that have both a department and a customer. Joins put tables side by side; set operators put results on top of each other.

The idea

Write two SELECTs with the same number of columns, of matching types, and put a set operator between them. The column names come from the first SELECT.

UNION ALL simply stacks the rows — fast, and keeps duplicates. UNION stacks them and removes duplicate rows — which costs a sort or hash. INTERSECT keeps rows in both results; EXCEPT keeps rows of the first that are not in the second.

A single ORDER BY at the very end sorts the combined result.

Two class lists. UNION ALL staples them together. UNION staples them and crosses out anyone listed twice. INTERSECT is pupils on both lists. EXCEPT is pupils on the first list but not on the second.

Pupils are matched by name. Set operators match whole rows, column by column — so adding a column that differs (like a source label) stops rows from counting as duplicates.

Syntax

SELECT col_a, col_b FROM table_one
UNION ALL
SELECT col_x, col_y FROM table_two
ORDER BY col_a;

How it works

  1. Both SELECTs return one column: department locations and customer cities.
  2. UNION stacks them (10 rows) and removes duplicate rows, leaving 5 different cities, including NULL.
  3. UNION ALL keeps all of them; a source column shows where each row came from.
  4. INTERSECT keeps only cities in both lists: Leeds and London.
  5. EXCEPT keeps department cities with no customer: Manchester.

Key points

Common mistakes

Not true: UNION matches the columns of its two queries by name.
In fact: Columns are matched by position, not by name. If the second query lists them in another order, the values land in the wrong columns.

In the interview

Set operators combine two result sets vertically, matching columns by position. UNION ALL appends everything; UNION also removes duplicate rows, which costs a sort or hash, so I default to UNION ALL unless duplicates must go. INTERSECT returns rows present in both results and EXCEPT returns rows in the first but not the second — a neat alternative to an anti-join when comparing id lists.

Part of Unit 4: Subqueries, CTEs and set operations in the free SQL course.