---
title: "UNION vs UNION ALL in SQL: Duplicates, Speed, Pitfalls"
description: "UNION stacks two result sets and then removes duplicate rows, like a DISTINCT over the whole output."
canonical: "https://www.aifordatabase.com/blog/union-vs-union-all-sql/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-11-06
updated: 2026-11-06
---

# 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](https://www.postgresql.org/docs/current/sql-select.html)). 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

| Criterion | UNION | UNION ALL |
| --- | --- | --- |
| Duplicate rows | Removed (whole result, both inputs) | Kept |
| Extra work | Sort or hash over every output row | None, rows are appended |
| Typical speed | Slower, grows with result size | Faster |
| Memory / temp space | Needs space for the dedupe step | Streams rows |
| Row count | Less than or equal to the sum of inputs | Exactly the sum of inputs |
| NULLs | Two all-NULL rows count as duplicates | Both kept |
| Safe for SUM / COUNT afterwards | Only if duplicates are truly bogus | Yes |
| Explicit spelling | `UNION DISTINCT` (PostgreSQL, MySQL) | `UNION ALL` |

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](https://www.postgresql.org/docs/current/queries-union.html) 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](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql) 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
```

![UNION collapses Ana's three identical $49 rows into one, UNION ALL keeps all five rows](https://cdn.sanity.io/images/upsr56wu/production/c8e1e71671a8b9dc8e09e44cd74dadc4d5b68813-1344x502.png?w=1280)

FIG 01 — Same inputs: UNION returns 3 rows, UNION ALL returns 5.

## 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.

![UNION adds a dedupe step after appending rows, UNION ALL returns appended rows directly](https://cdn.sanity.io/images/upsr56wu/production/24478bce386301241d01bc78962a2377689ce6e2-1344x326.png?w=1280)

FIG 02 — UNION ALL stops at Append; the dedupe step is the whole cost difference.

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)
```

![Summing a UNION reports $167 because two of Ana's $49 payments were removed; UNION ALL reports $265](https://cdn.sanity.io/images/upsr56wu/production/d7768709874a162d4c47e1eaaa26ecd8c0e292f1-1344x318.png?w=1280)

FIG 03 — The $98 gap is two legitimate payments that UNION treated as duplicates.

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](https://www.aifordatabase.com/blog/find-duplicates-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](https://www.aifordatabase.com/blog/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](https://www.aifordatabase.com/blog/sql-select-from-multiple-tables-without-join-2026/) covers when stacking is the right tool.

VIDEO · [SQL UNION vs UNION ALL: Understand the Difference with 2 Examples](https://www.youtube.com/watch?v=f5smzsbHSyA) (Ryan & Matt Data Science)

## 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](https://www.aifordatabase.com/blog/sql-cheat-sheet/) and [cumulative sum guide](https://www.aifordatabase.com/blog/cumulative-sum-in-sql/) are good next steps.

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### Is UNION the same as UNION DISTINCT?

Yes. In PostgreSQL and MySQL, UNION DISTINCT is an explicit spelling of the default behaviour. PostgreSQL's docs call DISTINCT a "noise word" here. SQL Server accepts only UNION and UNION ALL.

### Does UNION treat NULLs as equal?

Yes, for deduplication. Two rows that match column by column, with NULL in the same positions, count as duplicates and only one survives. SELECT NULL UNION SELECT NULL returns one row; with UNION ALL it returns two.

### Can I use ORDER BY in each part of a UNION?

Not directly. A single ORDER BY after the last query sorts the whole result. To order or limit one input, wrap that SELECT in parentheses, as in the top-5-per-source example above.

### Do the column names have to match?

No. Only the count and types have to line up. The result takes its column names from the first SELECT, so the names in later queries are ignored.

### Is UNION ALL always faster than UNION?

On the same inputs, UNION ALL is never slower, because UNION does the same appending plus a dedupe step. The size of the gap depends on how many rows the union produces; check it with EXPLAIN ANALYZE.

### Does UNION remove duplicates from only the second query?

No. It removes duplicates across the entire combined result, including duplicates that exist inside the first query alone.

Read next

*   [METRICFLOW RESOURCES: 18 DOCS, REPOS AND TOOLS WORTH USING →](https://www.aifordatabase.com/blog/metricflow/)
*   [POSTGRES LIST ALL DATABASES: PSQL, SQL AND GUI METHODS →](https://www.aifordatabase.com/blog/postgres-list-all-databases/)
*   [WHAT IS DIMENSIONAL MODELING? KIMBALL METHOD EXPLAINED →](https://www.aifordatabase.com/blog/dimensional-modeling/)

Source: https://www.aifordatabase.com/blog/union-vs-union-all-sql/