UNION, INTERSECT & EXCEPT

Set operations combine whole result sets. UNION and UNION ALL stack rows, INTERSECT keeps rows both results share, and EXCEPT subtracts one result from another.

The key shift is shape: joins combine columns side by side; set operations combine compatible grids row by row.

Stacking result sets with UNION

Start with a common reporting task: combine rows from two sources. Here the café has a tiny counter feed and a mobile feed, each shaped as item_name, amount.

WITH counter_sales AS (
  SELECT v.item_name, v.amount
  FROM (VALUES
    ('Espresso', 2.50),
    ('Cookie', 2.80)
  ) AS v(item_name, amount)
),
mobile_sales AS (
  SELECT v.item_name, v.amount
  FROM (VALUES
    ('Cookie', 2.80),
    ('Matcha Latte', 4.20)
  ) AS v(item_name, amount)
)
SELECT cs.item_name, cs.amount
FROM counter_sales AS cs
UNION
SELECT ms.item_name, ms.amount
FROM mobile_sales AS ms
ORDER BY item_name;

  item_name   | amount
--------------+--------
 Cookie       |   2.80
 Espresso     |   2.50
 Matcha Latte |   4.20
(3 rows)

That grid is tidy, plausible, and wrong if you meant "all source rows." Cookie appeared in both feeds, but UNION returned only one Cookie row because UNION removes duplicate result rows.

WITH counter_sales AS (
  SELECT v.item_name, v.amount
  FROM (VALUES
    ('Espresso', 2.50),
    ('Cookie', 2.80)
  ) AS v(item_name, amount)
),
mobile_sales AS (
  SELECT v.item_name, v.amount
  FROM (VALUES
    ('Cookie', 2.80),
    ('Matcha Latte', 4.20)
  ) AS v(item_name, amount)
)
SELECT 'counter' AS source, cs.item_name, cs.amount
FROM counter_sales AS cs
UNION ALL
SELECT 'mobile' AS source, ms.item_name, ms.amount
FROM mobile_sales AS ms
ORDER BY item_name, source;

 source  |  item_name   | amount
---------+--------------+--------
 counter | Cookie       |   2.80
 mobile  | Cookie       |   2.80
 counter | Espresso     |   2.50
 mobile  | Matcha Latte |   4.20
(4 rows)

UNION ALL is not a sloppy version of UNION. It is the honest version when the question is "append these rows." UNION is the deliberate version when the question is "give me unique rows across these sources."

Column count and type rules

Set operations work by lining up columns by position: first with first, second with second, and so on. The column names in the final grid come from the first SELECT. Every branch must return the same number of columns, and corresponding columns must have compatible types.

Postgres refuses mismatched column counts:

SELECT 'counter' AS source, 'Cookie' AS item_name
UNION ALL
SELECT 'mobile' AS source, 'Cookie' AS item_name, 2.80 AS amount;

ERROR:  each UNION query must have the same number of columns
LINE 3: SELECT 'mobile' AS source, 'Cookie' AS item_name, 2.80 AS amount
               ^

The fix is to decide the output contract. If the combined result is source, item_name, amount, every branch must select those three columns in that order. If an old source lacks a value, use an explicit placeholder with a matching name and type.

INTERSECT and EXCEPT: comparing sets

INTERSECT answers "which rows appear in both results?" Use it for overlap: items sold in two periods, members present in two cohorts, ids present in two shards.

WITH april_items AS (
  SELECT v.item_name
  FROM (VALUES
    ('Espresso'),
    ('Cookie'),
    ('Cold Brew'),
    ('Matcha Latte')
  ) AS v(item_name)
),
may_items AS (
  SELECT v.item_name
  FROM (VALUES
    ('Cookie'),
    ('Cold Brew'),
    ('Flat White'),
    ('Orange Juice')
  ) AS v(item_name)
)
SELECT ai.item_name
FROM april_items AS ai
INTERSECT
SELECT mi.item_name
FROM may_items AS mi
ORDER BY item_name;

 item_name
-----------
 Cold Brew
 Cookie
(2 rows)

EXCEPT answers "which rows are in the first result but not the second?" Direction matters: April except May is not the same question as May except April.

WITH april_items AS (
  SELECT v.item_name
  FROM (VALUES
    ('Espresso'),
    ('Cookie'),
    ('Cold Brew'),
    ('Matcha Latte')
  ) AS v(item_name)
),
may_items AS (
  SELECT v.item_name
  FROM (VALUES
    ('Cookie'),
    ('Cold Brew'),
    ('Flat White'),
    ('Orange Juice')
  ) AS v(item_name)
)
SELECT ai.item_name
FROM april_items AS ai
EXCEPT
SELECT mi.item_name
FROM may_items AS mi
ORDER BY item_name;

  item_name
--------------
 Espresso
 Matcha Latte
(2 rows)

Like UNION, these operators use set-style distinct rows by default. That is usually what you want for overlap and difference questions: "which item names are shared?" not "how many times did each name appear?" When multiplicity matters, reach for the ALL forms deliberately, and test the row counts.

Practical pattern: periods, sources, shards

Common set-operation questions sound like this:

• Periods: stack January and February reports with UNION ALL, or compare April and May item lists with INTERSECT and EXCEPT.
• Sources: append counter, mobile, and delivery feeds while preserving a source column.
• Shards: run the same shaped query against region tables, then combine the compatible results.

The repeated phrase is same shaped query. Before a set operation, make each branch return the same business columns in the same order. Put one final ORDER BY at the end to sort the combined result.

Try the operators together:

Section 7 gave you the read-side composition tools: subqueries, correlated existence checks, named pipelines, and set operations. Next section starts writing data, where the same discipline matters more: every change needs an exact target and a way to verify what happened.