Skip to content
Ephizen logo ephizen / wiki
CS 303

Set Operations — UNION / INTERSECT / EXCEPT

Stack two query results vertically — UNION glues, INTERSECT keeps the overlap, EXCEPT subtracts.

SQL I — Reading Data beginner #sql #queries

TL;DR

JOIN glues columns from two tables side-by-side. Set operations stack rows from two query results vertically:

  • UNION — combines two result sets, removes duplicates. SQL set semantics.
  • UNION ALL — combines, keeps duplicates. Faster (no dedup).
  • INTERSECT — rows that appear in both queries.
  • EXCEPT — rows in the first query that don’t appear in the second. (MINUS in Oracle.)

The two queries must produce the same number of columns of compatible types in the same order. Column names come from the first query.

The single most common gotcha: writing UNION when you meant UNION ALL. UNION does a DISTINCT over the combined rows, which can be very expensive on large data. Most of the time you want UNION ALL.

A worked example

CREATE TABLE current_users  (id int, name text);
CREATE TABLE archived_users (id int, name text);

INSERT INTO current_users  VALUES (1, 'Alice'), (2, 'Bob'),  (3, 'Carol');
INSERT INTO archived_users VALUES (3, 'Carol'), (4, 'Dave'), (5, 'Eve');

UNION ALL — stack everything

SELECT id, name FROM current_users
UNION ALL
SELECT id, name FROM archived_users;
+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Bob   |
| 3  | Carol |
| 3  | Carol |   ← duplicated!
| 4  | Dave  |
| 5  | Eve   |
+----+-------+

Six rows out (3 + 3). Carol appears twice — UNION ALL doesn’t dedup.

UNION — stack and dedup

SELECT id, name FROM current_users
UNION
SELECT id, name FROM archived_users;
+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Bob   |
| 3  | Carol |   ← single row
| 4  | Dave  |
| 5  | Eve   |
+----+-------+

Five rows. Carol appears once. The dedup is by full row — if the same id had different names in the two tables, you’d get both. UNION adds an implicit sort or hash to dedup, which on a billion rows is the difference between a 1-second and 5-minute query.

Rule of thumb: if you know the inputs are disjoint (no row appears in both), use UNION ALL. If you don’t know but you’d be fine with duplicates, use UNION ALL and skip the dedup. Use UNION only when you actually need set semantics.

INTERSECT — rows in both

SELECT id, name FROM current_users
INTERSECT
SELECT id, name FROM archived_users;
+----+-------+
| id | name  |
+----+-------+
| 3  | Carol |
+----+-------+

Only Carol (id=3) is in both tables.

EXCEPT — rows in A not in B

SELECT id, name FROM current_users
EXCEPT
SELECT id, name FROM archived_users;
+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Bob   |
+----+-------+

Alice and Bob — current users who are not also in the archive.

EXCEPT is also great for diffing two query results during data migrations or ETL validation: “what rows did the new pipeline output that the old one didn’t?”

Type and column rules

Both queries must produce the same number of columns. The types must be compatible — Postgres tries to find a common supertype. int and numeric work; int and text won’t (you have to explicit-cast).

Column names come from the first query. Aliases on the second query are ignored:

SELECT id, name AS user_name FROM current_users
UNION ALL
SELECT id, name AS handle    FROM archived_users;
-- output columns: id, user_name (the alias from query 2 is ignored)

You can ORDER BY the combined result, but only at the very end:

SELECT id FROM a
UNION ALL
SELECT id FROM b
ORDER BY id DESC;

You can’t ORDER BY an individual sub-SELECT (Postgres allows it inside parens; not standard).

INTERSECT ALL and EXCEPT ALL — multiset semantics

Less commonly used: the ALL variants preserve duplicates.

{1, 2, 2, 3} INTERSECT {2, 2, 3, 4} → {2, 3} (set intersection)

{1, 2, 2, 3} INTERSECT ALL {2, 2, 3, 4} → {2, 2, 3} (multiset)

Useful if you care about row counts on both sides — e.g. “items appearing in both inventory snapshots, accounting for quantity”.

When to use sets vs OR / IN / JOIN

A common antipattern: writing a UNION of three nearly-identical queries when one query with WHERE … OR … would do.

-- Antipattern: 3 scans of the same table
SELECT * FROM events WHERE event_type = 'click'
UNION ALL
SELECT * FROM events WHERE event_type = 'view'
UNION ALL
SELECT * FROM events WHERE event_type = 'purchase';

-- Better: 1 scan with an OR / IN
SELECT * FROM events WHERE event_type IN ('click', 'view', 'purchase');

When the queries differ meaningfully (different sources, different transforms), UNION ALL is right. When you’re partitioning a single table by predicate, use IN or OR.

Common pitfalls

  • UNION instead of UNION ALL over partitioned data. If you’ve split a table into daily partitions and want to read a month, UNION ALL the reads. Using UNION adds a wasteful dedup over 30 already-disjoint partitions.
  • NULLs in UNION dedup. Two rows with (1, NULL) and (1, NULL) are considered duplicates by UNION (and by INTERSECT/EXCEPT), even though NULL = NULL is unknown. This is a deliberate special case in the SQL standard.
  • Column-order mismatch. SELECT a, b … UNION SELECT b, a doesn’t error — types may match — and silently swaps the columns.
  • Forgetting the column-count must match. If you add a column to query 1 and forget to add it to query 2, you get an error. Less often: you add a NULL to query 2 to satisfy the count, and the resulting type is unclear.
  • ORDER BY placement. Only at the end, applies to the whole result. Wrapping each sub-SELECT in parens to apply ORDER BY per side is a Postgres extension, not standard, and rarely what you actually want.

Production patterns for ML

1. Combining historical + real-time event streams. Online features are often computed from a UNION of yesterday’s batch table and today’s real-time table:

WITH all_events AS (
    SELECT user_id, event, ts FROM events_batch
        WHERE ts < CURRENT_DATE
    UNION ALL
    SELECT user_id, event, ts FROM events_realtime
        WHERE ts >= CURRENT_DATE
)
SELECT user_id, COUNT(*) AS events_30d
FROM   all_events
WHERE  ts >= CURRENT_DATE - INTERVAL '30 days'
GROUP  BY user_id;

UNION ALL because the two tables are disjoint by construction (< vs >= on the same boundary).

2. ETL diff for parity checks. Comparing a new pipeline’s output to the legacy one:

-- Rows the new pipeline produces but legacy doesn't
SELECT * FROM new_features  EXCEPT SELECT * FROM legacy_features;
-- Rows legacy produces but new doesn't
SELECT * FROM legacy_features EXCEPT SELECT * FROM new_features;

If both queries return zero rows, your pipelines agree. If not, the diffs are the bugs.

Resources