Level 2 · Merge two audiences with UNION

UNION ALL keeps every row from every branch. UNION (without ALL) does the same stacking and then removes duplicate rows, as if you had wrapped the result in SELECT DISTINCT. Choosing between them is a question about your data, not about style.

Same data, two answers

Jordan wants one row per holiday customer. Counting the two ways on Harbor & Pine:

SELECT COUNT(*) FROM (
  SELECT customer_id FROM orders
  WHERE status = 'completed' AND order_date >= '2025-11-01' AND order_date < '2025-12-01'
  UNION
  SELECT customer_id FROM orders
  WHERE status = 'completed' AND order_date >= '2025-12-01' AND order_date < '2026-01-01'
) AS holiday;

With UNION the count is 745. With UNION ALL it is 1,285, which is exactly the number of completed orders in those two months: each branch returns one row per order, not per customer. Separately, 442 customers bought in November and 455 in December, so 442 + 455 − 745 = 152 people bought in both.

Note that UNION removed two kinds of duplicates here: repeats inside one branch (a customer with three November orders) and repeats across branches (a customer in both months). It compares whole rows, so customer_id plus segment must match. If you added order_date to the SELECT, almost no rows would be equal any more and the duplicates would come back.

Customers with and without orders

UNION ALL with a status label builds a classification list from two disjoint sets:

SELECT customer_id, 'with orders' AS status
FROM customers WHERE customer_id IN (SELECT customer_id FROM orders)
UNION ALL
SELECT customer_id, 'without orders'
FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);

That gives 1,771 customers with orders and 247 without (all 2,018 customers). The two sets cannot overlap, so UNION ALL is right, and it skips the work of looking for duplicates.

Which one to use

• The sets overlap and you want each row once (an audience, a list of ids): UNION.
• You are stacking events or amounts that must all be counted (orders, refunds, sessions): UNION ALL.
• The sets cannot overlap (labelled, disjoint groups): UNION ALL, because it is faster.

Common mistakes

• UNION ALL for a people list, so customers appear once per order and get two emails.
• UNION on amounts, which silently merges two different refunds of $49.00 into one. Level 5 of this chapter is about exactly that bug.
• Expecting UNION to dedupe on one column. It compares all selected columns together.

In SQL Server

UNION and UNION ALL behave identically. To preview the merged list you would add TOP 10 after the first SELECT keyword in a wrapping query, where DuckDB and Postgres use LIMIT 10 at the end.