Back to Notes

SQL UNION & Set Operations

Combine results from multiple SELECT statements — all must have the same column count with compatible types.

UNION vs UNION ALL

-- UNION — deduplicates (sort/hash under the hood — real cost on large sets)
SELECT city FROM customers UNION SELECT city FROM suppliers;

-- UNION ALL — keeps duplicates, no dedup step, faster
SELECT id, amount, 'payment' AS type FROM payments
UNION ALL
SELECT id, amount, 'refund' AS type FROM refunds;

Default to UNION ALL unless deduplication is actually required — the sort/hash step UNION performs to remove duplicates is real, avoidable overhead when you already know the two sources can't overlap (e.g., combining historical-archive rows with current rows on a date boundary).

INTERSECT & EXCEPT

-- Rows in both sets
SELECT product_id FROM orders WHERE user_id = 1
INTERSECT
SELECT product_id FROM orders WHERE user_id = 2;

-- Rows in the first set but not the second (MINUS in Oracle)
SELECT product_id FROM orders WHERE user_id = 1
EXCEPT
SELECT product_id FROM orders WHERE user_id = 2;

Both are expressible via JOIN/anti-join too (see [[SQL JOINs]]) — INTERSECT/EXCEPT are simply more direct when the intent genuinely is set comparison rather than row-level combination.

Practical Patterns

-- Combine same-shape data across regions
SELECT user_id, name, 'US' AS region FROM users_us
UNION ALL
SELECT user_id, name, 'EU' AS region FROM users_eu;

-- Historical + current, single ordered result
SELECT order_id, total, created_at FROM orders_archive WHERE created_at < '2025-01-01'
UNION ALL
SELECT order_id, total, created_at FROM orders WHERE created_at >= '2025-01-01'
ORDER BY created_at;

Gotchas

SELECT NULL UNION SELECT NULL;   -- 1 row, not 2 — UNION treats NULL = NULL for dedup purposes
SELECT 1 UNION SELECT 1.5;       -- works — types are promoted to a common compatible type

SELECT a, b FROM t1 UNION SELECT a FROM t2;         -- ERROR — column count mismatch
SELECT a, b FROM t1 UNION SELECT a, NULL FROM t2;   -- fix: pad with NULL to match column count

Column headers in the combined result come from the first SELECT's aliases — the second query's column names are ignored for display purposes even though its values are included.

Interview Drills

  1. Why should UNION ALL be the default choice over UNION unless deduplication is explicitly needed? — UNION performs a sort or hash pass specifically to detect and remove duplicate rows across the combined result — real, avoidable cost when the two source sets are already known not to overlap (e.g., a date-partitioned split between archive and current tables).
  2. Why does SELECT a, b FROM t1 UNION SELECT a FROM t2 fail? — UNION requires every participating SELECT to return the same number of columns with compatible types — the second query returns one column while the first returns two; padding the shorter query with an explicit NULL placeholder resolves the mismatch.

Cross-links

[[SQL JOINs]] · [[SQL Basics & Query Fundamentals]] · [[SQL Pivoting]]