UNION, INTERSECT and EXCEPT

Stacking results with UNION

UNION puts the rows of one query under the rows of another. Every city customers live in, from the early customers and the newer ones:

SELECT city FROM customers WHERE joined_on < '2025-04-01'
UNION
SELECT city FROM customers WHERE joined_on >= '2025-04-01'
ORDER BY city;

   city
----------
 Karachi
 Lagos
 Pune
 Seoul
 Shanghai

(6 rows)

Pune appears once though two customers live there: UNION removes duplicate rows. UNION ALL keeps them all and is cheaper, because it skips the duplicate check:

SELECT count(*) FROM (SELECT city FROM customers UNION ALL SELECT city FROM customers) t;  -- 16
SELECT count(*) FROM (SELECT city FROM customers UNION     SELECT city FROM customers) t;  -- 6

Use UNION ALL unless you actually want duplicates removed.

The NULL exception

Look at the last row of the first result: one empty city. Elena and Hugo both have NULL cities, yet UNION kept only one NULL row. For removing duplicates, in UNION, INTERSECT, EXCEPT and DISTINCT, NULLs count as equal. GROUP BY does the same, putting all NULLs in one group. It is the opposite of what = does, so it is worth remembering when results do not add up.

Rows in both, rows in one

INTERSECT keeps rows that appear in both results. Customers who bought both accessories and furniture:

SELECT o.customer_id FROM orders o
JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id
WHERE p.category = 'Accessories'
INTERSECT
SELECT o.customer_id FROM orders o
JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id
WHERE p.category = 'Furniture';

Customers 4 and 6.

EXCEPT keeps rows of the first result that are not in the second. Products never ordered:

SELECT id FROM products
EXCEPT
SELECT product_id FROM order_items;

Product 11, the cable organiser: the same answer as the NOT EXISTS query in Joins.

Building a feed from different tables

UNION ALL is how you merge different kinds of event into one list. Both sides must return the same number of columns, with compatible types:

SELECT 'joined' AS event, name AS who, joined_on::timestamptz AS at FROM customers
UNION ALL
SELECT 'ordered', c.name, o.ordered_at FROM orders o JOIN customers c ON c.id = o.customer_id
ORDER BY at DESC
LIMIT 5;

  event  |     who     |           at
---------+-------------+------------------------
 ordered | Farhan Ali  | 2025-09-09 05:00:00+00
 ordered | Divya Iyer  | 2025-08-28 14:25:00+00
 ordered | Chen Wei    | 2025-08-02 05:25:00+00
 joined  | Hugo Martin | 2025-07-30 00:00:00+00
 ordered | Grace Kim   | 2025-07-19 11:10:00+00

The column names come from the first query, and ORDER BY and LIMIT at the end apply to the combined result. When the shapes do not match, PostgreSQL says so:

ERROR:  each UNION query must have the same number of columns
ERROR:  UNION types text and numeric cannot be matched

Check yourself

1. What is the difference between UNION and UNION ALL, and which should you default to?

   Show answer

   UNION removes duplicate rows; UNION ALL keeps every row and skips the work of checking. Default to UNION ALL unless you need duplicates removed.

   I could explain this without looking

2. Two customers have a NULL city. How many NULL rows does SELECT city FROM customers UNION SELECT city FROM customers return?

   Show answer

   One. For duplicate elimination, NULLs are treated as equal to each other.

   I could explain this without looking

3. Write “products that were never ordered” with a set operation.

   Show answer

   SELECT id FROM products EXCEPT SELECT product_id FROM order_items;

   I could explain this without looking