SQL: UNION ALL Keeps Duplicate Rows

The symptom

You combine two queries, then notice duplicate rows in the result. The query is not broken: UNION ALL is doing exactly what it promises by stacking both result sets without deduplication.

Why it happens

UNION ALL is an append operation. If the same row appears in both inputs, it appears twice in the output. That is useful for audit logs, event streams, and any report where duplicate rows represent real activity.

It is wrong when the question is about unique entities, such as one row per customer, product, page, or date.

The fix

Use UNION when duplicate result rows should be removed, and keep UNION ALL only when a repeated row stands for something that really happened twice. Both queries below stack the customers behind shipped Garden Shop orders onto the customers who used a coupon. Those are two filters over the same orders table, so the lists overlap.

select customer_id
from orders
where status = 'shipped'
union all
select customer_id
from orders
where coupon_code is not null
order by customer_id

select customer_id
from orders
where status = 'shipped'
union
select customer_id
from orders
where coupon_code is not null
order by customer_id

Theo Brandt, customer 1, accounts for four of the appended rows on his own: two shipped orders, plus two orders carrying a coupon code, one of which is also one of the shipped ones. UNION collapses the stack to the 14 distinct customers behind it. When you want that collapse but also want control over what else the query does, keep UNION ALL and deduplicate explicitly with distinct or a grouping query.

Learn more

• Lesson 15.1 · UNION ALL
• Lesson 15.2 · UNION
• Reference · Set operations
• Cheatsheet · Set operations