SQL UNION vs UNION ALL Explained

UNION and UNION ALL both stack the results of multiple SELECT statements into a single result set, but they behave very differently once duplicate rows show up — and that difference has a real performance cost. This guide explains exactly what each keyword does, walks through concrete examples, and shows why picking the wrong one can silently slow down a query on a large dataset.

The Basic Rules for Combining SELECT Statements

Both UNION and UNION ALL combine two or more SELECT statements vertically — stacking rows on top of each other rather than joining columns side by side. Two rules apply to both:

• Each SELECT must return the same number of columns
• Corresponding columns must have compatible data types (e.g. both numeric, or both text)
• Final column names come from the first SELECT statement

SELECT customer_name, email FROM online_customers
UNION
SELECT customer_name, email FROM store_customers;

UNION — Removes Duplicate Rows

UNION combines the result sets and then removes any rows that are exact duplicates across the whole combined set:

-- online_customers has: ('Aisha Khan', 'aisha@example.com')
-- store_customers has:   ('Aisha Khan', 'aisha@example.com')  -- same person, both channels

SELECT customer_name, email FROM online_customers
UNION
SELECT customer_name, email FROM store_customers;

-- Result: 'Aisha Khan' appears only ONCE, even though she is
-- in both source tables

To eliminate duplicates, the database must compare every row against every other row — typically by sorting the combined result set or building a hash table of rows seen so far. This is exactly the extra work that UNION ALL skips.

UNION ALL — Keeps Every Row, Including Duplicates

UNION ALL simply appends the result sets one after another. No comparison, no deduplication — every row from every SELECT is kept:

SELECT customer_name, email FROM online_customers
UNION ALL
SELECT customer_name, email FROM store_customers;

-- Result: 'Aisha Khan' appears TWICE — once from each table

This matters for correctness, not just speed. If you are summing amounts across two tables, UNION ALL is usually what you actually want — UNION could silently drop a legitimate row that happens to look identical to another one:

-- Two separate $50 payments that happen to share the same
-- customer_id, amount, and payment_date would be MERGED into
-- one row by UNION -- silently losing a real payment.

SELECT customer_id, amount, payment_date FROM card_payments
UNION ALL
SELECT customer_id, amount, payment_date FROM bank_payments;

-- UNION ALL preserves both real payments correctly.

Performance: Why UNION ALL Is Faster

UNION ALL is almost always faster than UNION on the same data, because of the extra work UNION does internally:

1. UNION ALL — reads rows from each SELECT and streams them straight into the output. No extra pass over the data.
2. UNION — reads rows from each SELECT, then performs a sort or builds a hash set over the entire combined result to detect and drop duplicates, before returning the final rows.
3. The deduplication cost in UNION grows with the size of the combined result set — the bigger the tables, the bigger the performance gap.

The rule of thumb: if you already know the result sets cannot overlap (for example, two tables partitioned by region, or you have already filtered out matches), use UNION ALL. Only pay for deduplication with UNION when duplicates are actually possible and unwanted.

Ordering and Filtering a UNION Result

ORDER BY and LIMIT apply to the final combined result, not to each individual SELECT. Put them after the last SELECT statement:

SELECT product_name, price, 'clearance' AS source FROM clearance_items
UNION ALL
SELECT product_name, price, 'regular' AS source FROM regular_items
ORDER BY price DESC
LIMIT 10;

Adding a literal label column like 'clearance' AS source is a common pattern — it lets you tell which original table each row came from after the results are merged together.

Quick Decision Guide

• Combining sales from two regional tables into one report, no risk of overlap → UNION ALL
• Merging a mailing list from two sources where the same email might appear in both → UNION
• Need every transaction row preserved for an accurate SUM/COUNT → UNION ALL
• Building a distinct list of unique product categories across two catalogs → UNION

Frequently Asked Questions