SQL Mastery
Intermediate 12 min read SQL Mastery

Set Operations

Combine, keep, or subtract whole result sets

UNION, INTERSECT and EXCEPT combine whole result sets. UNION ALL preserves duplicates and rows; the others thin them — the classic reconciliation toolkit.

UNIONUNION ALLINTERSECTEXCEPTreconciliation
Prerequisites: SQL Query Fundamentals
Before we start

What you will learn

  • Choose UNION vs UNION ALL by duplication intent
  • Use EXCEPT to diff two tables
  • Use INTERSECT to find common rows
Introduction

What is this?

Set operations combine entire SELECT results vertically. UNION merges rows and removes duplicates; UNION ALL keeps everything. INTERSECT returns rows present in both; EXCEPT returns rows in the first but not the second. They are the fastest way to reconcile 'source says X, target says Y'.

Motivation

Why do you need it?

  • Reconciliation checks (diff source vs warehouse) are pure EXCEPT/diff queries.
  • UNION ALL append is how staging and daily loads physically combine partitioned data.
  • Understanding duplicate semantics prevents double-counting when merging sets.
Think about it

Mental model

Imagine

Two book shelves being merged

Think of

Set operations

UNION shelves the books once (no duplicates), UNION ALL copies every book even repeats, INTERSECT keeps only titles on both shelves, EXCEPT keeps titles only on the first.

Mechanics

How it works

  • UNION removes duplicate rows across the combined result; UNION ALL preserves every row.
  • Column counts and order must match in both SELECTs; typing is reconciled, not inferred.
  • EXCEPT compares whole rows — a single differing column disqualifies a match.
  • INTERSECT/EXCEPT can be phrased with NOT EXISTS but set ops are clearer and often optimized better.
  • ORDER BY applies once to the combined result.

Set operation row flows.

Result A
Result B
UNION / UNION ALL
INTERSECT
EXCEPT
Hands-on

Examples

Reconcile source vs warehouse

Reconcile source vs warehouse
-- rows in the source but missing from the warehouse
SELECT 'source_only' AS side, * FROM (
  SELECT id, amount FROM source_orders
  EXCEPT
  SELECT id, amount FROM warehouse_orders
) d;
-- rows in the warehouse but not the source:
SELECT 'warehouse_only', * FROM (
  SELECT id, amount FROM warehouse_orders
  EXCEPT
  SELECT id, amount FROM source_orders
) d;

The two EXCEPT directions name exactly where the drift lives — the fastest reconciliation primitive.

Real world

In production

  • A nightly reconciliation job EXCEPTs the warehouse against the source and pages on non-empty diffs.
  • A data merge uses UNION ALL by partition, then a dedup layer collapses overlaps.
  • A uniqueness audit INTERSECTs two candidate-join results to reveal exactly which rows made both filters.
Guidelines

Best practices

  • Default to UNION ALL in pipelines; add UNION only when dedup across sources is the actual spec.
  • Reconcile on modeled keys + values, not SELECT * (a reshuffle is a diff only where it matters).
  • Keep the two operands symmetric for EXCEPT diffs to be shallow and readable.
  • Document NULL semantics: EXCEPT treats NULLs as equal in some engines, unequal in others.
Watch out

Common mistakes

  • UNION where UNION ALL belonged — accidentally thinning a daily append and undercounting.
  • SELECT * in EXCEPT so a benign column (updated_at) makes everything 'differ'.
  • Mismatched column lists causing 'each UNION query must have the same number of columns'.
Root cause analysis

Common issues

Error
Reconciliation always reports drift
Root cause

Comparing volatile columns (like updated_at) or NULL type mismatches

Fix

Compare business keys + material totals only, and COALESCE nullable columns consistently.

Error
UNION returned too few rows
Root cause

UNION deduped rows that were genuinely repeated business transactions

Fix

Use UNION ALL if every row must survive; dedup later with row_number on the natural key.

When things break

Debugging tips

  • When a diff never empty, reduce the EXCEPT to a single column (id) to localize it.
  • Run each side's COUNT(*) first; if counts match but diff doesn't, values differ, not rows.
  • Check whether the planner treats NULLs consistently in your dialect before trusting EXCEPT as 'equal'.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which operation returns rows present in the first result but absent from the second?

2

UNION and UNION ALL return identical row counts for identical inputs.

Go deeper

Official documentation

PostgreSQL union/except/intersect
docs
SQLite compound select
docs