32 UNION, INTERSECT, EXCEPT

Set operations combine two result sets row-by-row. All three require the queries on either side to return the same number of columns with compatible types. UNION returns rows in either side (duplicates removed). INTERSECT returns rows in both. EXCEPT returns rows in the first that are not in the second. Each has an ALL variant. UNION ALL concatenates both inputs. INTERSECT ALL keeps the smaller occurrence count of each row; EXCEPT ALL subtracts the right count from the left, with a minimum of zero. The latter two still need to match and count rows.

What you’ll learn

• UNION vs UNION ALL and when each is appropriate
• INTERSECT and INTERSECT ALL
• EXCEPT and EXCEPT ALL
• Combining set operations and how precedence works
• Performance: when UNION ALL is meaningfully faster

Example

-- UNION removes duplicates across the combined result
SELECT 1 AS n UNION SELECT 2 UNION SELECT 1;

-- UNION ALL keeps duplicates (cheaper, no sort/hash)
SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 2;

-- INTERSECT: rows present in both
SELECT id FROM fruits WHERE id <= 3
INTERSECT
SELECT id FROM fruits WHERE id >= 2;

-- EXCEPT: rows in the first query but not the second
SELECT id FROM fruits WHERE id <= 4
EXCEPT
SELECT id FROM fruits WHERE id >= 3;

-- Order of evaluation: INTERSECT binds tighter than UNION / EXCEPT.
-- Use parentheses to be explicit:
(SELECT id FROM fruits WHERE id < 5)
UNION
(SELECT id FROM fruits WHERE id > 100);

-- ORDER BY applies to the combined result; column refs by position or alias
SELECT id AS n FROM fruits WHERE id < 3
UNION
SELECT id     FROM fruits WHERE id > 100
ORDER BY n;

UNION deduplicates by sort or hash on the full row — that work is real and shows up in EXPLAIN. If you know the inputs cannot duplicate (because the predicates are disjoint, for example), prefer UNION ALL. On large queries the difference can be substantial.

INTERSECT and EXCEPT are less common in application code but handy for analysis: “users who clicked A and B,” “products in catalog A that are not in catalog B.”

Set operations are set-of-rows operations. The column types must align across the inputs — int and text cannot be unioned directly. Names come from the first query’s column names.

Run the example

To run (requires fruits):

$ psql -f source/union-intersect-except.sql postgres
 n
---
 1
 2
(2 rows)

 n
---
 1
 1
 2
(3 rows)
 ...

Common pitfalls

• UNION instead of UNION ALL on queries that cannot produce duplicates wastes CPU on deduplication. Pick UNION ALL unless duplicates are possible and unwanted.
• Misaligned column counts or types: SELECT id, name FROM t UNION SELECT id FROM t2 is an error. Pad with NULL::text or align columns.
• ORDER BY after a set operation refers to the combined result; it cannot use table-qualified column names. Use positions (ORDER BY 1) or aliases from the first query.
• EXCEPT is sensitive to NULL: EXCEPT treats two NULLs as equal (like DISTINCT), so a row with NULL on the left and NULL on the right is removed. This is usually what you want — but worth remembering.

Downloads

SQL file: union-intersect-except.sql · All lesson files · Setup guide