UNION vs UNION ALL in SQL: Duplicates, Speed, Pitfalls

TL;DR: UNION stacks two result sets and then removes duplicate rows, like a DISTINCT over the whole output. UNION ALL stacks them and keeps every row, so it does less work and is usually faster. Use UNION ALL by default and switch to UNION only when you actually want duplicates gone.

The difference between UNION and UNION ALL in SQL is one step: deduplication. Both operators append the rows of a second SELECT to the rows of the first, and both need the same number of columns with compatible types. UNION then compares every output row and keeps one copy of each distinct row, which costs a sort or hash over the full result. UNION ALL returns everything as is. The PostgreSQL manual puts it plainly: "UNION ALL is usually significantly quicker than UNION; use ALL when you can" (PostgreSQL SELECT reference). The rest of this page shows what each one returns on real rows, what the dedupe looks like in a query plan, and the reporting case where UNION quietly deletes revenue.

UNION vs UNION ALL at a glance

Behaviour checked against the PostgreSQL 18, SQL Server and SQLite 3.46 documentation and test runs, November 2026.

What is the difference between UNION and UNION ALL?

UNION returns the set of distinct rows that appear in either query; UNION ALL returns every row from both queries, duplicates included. The PostgreSQL docs on combining queries say UNION "eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used." Microsoft's T-SQL UNION reference says the same: ALL "incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed."

Here is a small payments example. Ana paid $49 twice on the same day through Stripe (two real charges, say two seats), and once through PayPal.

-- stripe_payments: ('ana', 49, '2026-10-01'), ('ana', 49, '2026-10-01'), ('ben', 99, '2026-10-02')
-- paypal_payments: ('ana', 49, '2026-10-01'), ('cy', 19, '2026-10-03')

SELECT customer, amount, paid_on FROM stripe_payments
UNION
SELECT customer, amount, paid_on FROM paypal_payments
ORDER BY customer;
-- 3 rows: ana 49, ben 99, cy 19

SELECT customer, amount, paid_on FROM stripe_payments
UNION ALL
SELECT customer, amount, paid_on FROM paypal_payments
ORDER BY customer;
-- 5 rows: ana 49 (x3), ben 99, cy 19

Does UNION remove duplicates within one query?

Yes. UNION deduplicates the combined output, so duplicates inside a single input disappear too, not only rows that appear in both inputs. In the example above, Ana's two identical Stripe rows collapse even before the PayPal row is considered. You can see it with a query against itself:

SELECT customer FROM stripe_payments
UNION
SELECT customer FROM stripe_payments;
-- 2 rows: ana, ben  (stripe_payments alone has 3 rows)

This surprises people who expect UNION to mean "rows from A plus rows from B that are not already in A". That is closer to what UNION ALL plus a NOT EXISTS filter does. UNION is a set operation over the whole result.

Which is faster, UNION or UNION ALL?

UNION ALL is faster, because UNION has to find duplicates and UNION ALL does not. Finding duplicates means sorting the combined rows or building a hash table of them, and the cost grows with the number of rows and their width. The two plans differ by exactly that step.

In PostgreSQL, run EXPLAIN on both. The UNION ALL plan is an Append node over the two scans. The UNION plan puts a HashAggregate (or Sort plus Unique) on top of that Append, and on large results the sort can spill to disk. SQLite shows the same split in plain words:

EXPLAIN QUERY PLAN
SELECT x FROM a UNION SELECT x FROM b;
-- COMPOUND QUERY
--   LEFT-MOST SUBQUERY: SCAN a
--   UNION USING TEMP B-TREE: SCAN b

EXPLAIN QUERY PLAN
SELECT x FROM a UNION ALL SELECT x FROM b;
-- COMPOUND QUERY
--   LEFT-MOST SUBQUERY: SCAN a
--   UNION ALL: SCAN b

The "TEMP B-TREE" is the dedupe structure. UNION ALL has none.

How much slower UNION is depends on your data, so measure it on yours with EXPLAIN ANALYZE instead of trusting a generic benchmark. Two rules hold everywhere: the gap is small on a few hundred rows and grows with result size, and UNION can never be faster than UNION ALL on the same inputs, since it does the same work plus a step.

When should I use UNION ALL?

Use UNION ALL whenever duplicates are impossible or meaningful. That covers most real queries:

• The inputs cannot overlap. Combining orders_2025 and orders_2026, or rows tagged with a literal source column ('stripe' AS source), can never produce identical rows, so UNION's dedupe is pure cost.
• Duplicates are real events. Payments, page views, log lines and order items that happen to be identical are still separate events. Removing them changes the totals.
• You aggregate afterwards. If a SUM, COUNT or AVG sits on top of the union, UNION ALL is the only one that counts every row.
• Recursive CTEs that cannot cycle. PostgreSQL's recursive form is non_recursive_term UNION [ ALL | DISTINCT ] recursive_term; UNION ALL is the usual choice when the hierarchy has no loops, while UNION stops some cycles by discarding repeated rows.

Use UNION when you want a distinct list: every email address that appears in either the customers or the newsletter table, or the set of countries you sell in across two regions.

The reporting trap: UNION silently drops real rows

UNION's dedupe compares whole rows, and nothing warns you when it removes one. In the payments example, total revenue is $265 (49 + 49 + 99 + 49 + 19). Sum the UNION instead and you get $167:

SELECT SUM(amount) FROM (
  SELECT customer, amount, paid_on FROM stripe_payments
  UNION
  SELECT customer, amount, paid_on FROM paypal_payments
) t;
-- 167  (wrong: two real $49 payments were removed)

SELECT SUM(amount) FROM (
  SELECT customer, amount, paid_on FROM stripe_payments
  UNION ALL
  SELECT customer, amount, paid_on FROM paypal_payments
) t;
-- 265  (correct)

The bug hides because the rows only look identical: you selected too few columns to tell them apart. Two defenses:

1. Default to UNION ALL in any query that feeds a metric.
2. If you need UNION, include a key that makes real rows distinct (payment_id, a source literal), so only true duplicates collapse.

If you want to find duplicates rather than drop them, group and count instead; see our guide to finding duplicates with a SQL query.

Rules both operators share

Both operators have the same requirements, in every major database:

• Same number of columns, compatible types. PostgreSQL calls this "union compatible". SQL Server also requires the same column order and resolves differing types by data type precedence. Column names come from the first query, so alias there (more on aliases in SQL).
• ORDER BY goes at the end and sorts the whole result. Put it after the last SELECT. To sort or limit one input, wrap it in parentheses.
• LIMIT binds to the whole union unless you parenthesize. PostgreSQL documents that SELECT a FROM b UNION SELECT x FROM y LIMIT 10 means (SELECT a FROM b UNION SELECT x FROM y) LIMIT 10.
• No guaranteed order without ORDER BY. PostgreSQL notes there is "no guarantee" rows come back query 1 first, then query 2.
• Precedence. UNION and EXCEPT evaluate left to right; INTERSECT binds tighter, so q1 UNION q2 INTERSECT q3 means q1 UNION (q2 INTERSECT q3).

-- top 5 per source, then combined (parentheses keep LIMIT on each input)
(SELECT customer, amount FROM stripe_payments ORDER BY amount DESC LIMIT 5)
UNION ALL
(SELECT customer, amount FROM paypal_payments ORDER BY amount DESC LIMIT 5)
ORDER BY amount DESC;

A UNION is also not a JOIN: UNION stacks rows vertically from queries with the same shape, a JOIN puts columns side by side from related tables. Our page on selecting from multiple tables without a JOIN covers when stacking is the right tool.

Choose UNION if / choose UNION ALL if

Choose UNION if you need a distinct list across sources, and the selected columns fully identify what "the same row" means for your question.

Choose UNION ALL if the inputs cannot overlap, duplicates are real events, or the result feeds a SUM, COUNT or dashboard metric. When in doubt, pick UNION ALL and dedupe explicitly with DISTINCT or GROUP BY where you mean it, which also makes the intent visible to the next reader.

Where this shows up in reporting

Combining payment processors, regional databases or yearly archive tables is one of the most common reasons teams write a UNION, and one of the easiest places for a wrong total to reach a dashboard. If you'd rather ask "total revenue across Stripe and PayPal last month" in plain English and check the generated SQL before trusting the number, AI for Database shows the query behind every answer and keeps the result on a self-refreshing dashboard. Try AI for Database free at aifordatabase.com. For writing the queries yourself, our SQL cheat sheet and cumulative sum guide are good next steps.