UNION vs UNION ALL in SQL (Plus INTERSECT, EXCEPT)
· 7 min read
UNION and UNION ALL both stack the results of two queries on top of each other. The difference is one step: UNION removes duplicate rows from the combined result, and UNION ALL keeps every row.
SELECT email FROM online_orders
UNION ALL
SELECT email FROM store_orders;So the short answer to union vs union all is: use UNION ALL unless you actually want duplicates removed. It is faster (no de-duplication step) and it never loses rows you meant to keep. The rest of this guide shows both on the same data, the total that quietly shrinks with UNION, and the two related operators, INTERSECT and EXCEPT.
The example tables
A shop sells online and in a physical store. Each channel has its own table.
| order_id | amount | |
|---|---|---|
| 1 | ana@acme.io | 40.00 |
| 2 | ben@birch.co | 25.00 |
| 3 | ana@acme.io | 15.00 |
| 4 | cara@cobalt.dev | 40.00 |
| receipt_id | amount | |
|---|---|---|
| 901 | ben@birch.co | 25.00 |
| 902 | dev@dune.studio | 60.00 |
Ana ordered online twice. Ben bought online and in the store, for the same amount both times.
UNION ALL keeps every row
The query at the top returns all six emails, one per order:
Four rows from the first query, then two from the second. Without an ORDER BY the database may return them in any order, so add one when the order matters.
UNION removes duplicates
Swap in UNION and sort the result:
SELECT email FROM online_orders
UNION
SELECT email FROM store_orders
ORDER BY email;Four rows: each customer once. Notice that UNION removed Ana's second row too, even though both of Ana's rows came from the same table. UNION de-duplicates the whole combined result, not just rows that appear on both sides. It behaves like UNION ALL followed by SELECT DISTINCT.
This is the right tool when you want a distinct list, such as "every customer who bought through any channel".
The total that drops: why UNION can be wrong
The trouble starts when you combine rows you are going to add up. Here is the same total built both ways:
SELECT
(SELECT SUM(amount) FROM (
SELECT email, amount FROM online_orders
UNION ALL
SELECT email, amount FROM store_orders) AS t) AS total_union_all,
(SELECT SUM(amount) FROM (
SELECT email, amount FROM online_orders
UNION
SELECT email, amount FROM store_orders) AS t) AS total_union;| total_union_all | total_union |
|---|---|
| 205.00 | 180.00 |
Real revenue is 120 online plus 85 in the store, which is 205. The UNION version is 25 short. Ben's online order and his store receipt are the same (email, amount) pair, so UNION treated them as one row and threw the second away. Nothing errors and nothing warns you.
How to spot it: whenever a UNION feeds a SUM, COUNT or AVG, ask whether two different events could produce identical rows. If they could, you need UNION ALL. A quick check is to compare COUNT(*) of the combined result with the row counts of the two inputs added together. AI assistants often write UNION by default because it is the shorter word, so this is worth checking in any generated query.
Add a label column to keep the source
When you stack two tables, you usually want to know where each row came from. Add a constant column:
SELECT 'online' AS channel, order_id AS ref, email, amount FROM online_orders
UNION ALL
SELECT 'store', receipt_id, email, amount FROM store_orders
ORDER BY channel, ref;| channel | ref | amount | |
|---|---|---|---|
| online | 1 | ana@acme.io | 40.00 |
| online | 2 | ben@birch.co | 25.00 |
| online | 3 | ana@acme.io | 15.00 |
| online | 4 | cara@cobalt.dev | 40.00 |
| store | 901 | ben@birch.co | 25.00 |
| store | 902 | dev@dune.studio | 60.00 |
Three rules show up here:
- Same number of columns. Each
SELECTmust return the same count, in the same order. - Names come from the first query. The second query's columns are matched by position, so
receipt_idlands inref. Aliases in the secondSELECTare ignored. - One ORDER BY, at the end. It sorts the combined result.
The combined result works like any table, so you can wrap it and aggregate, just as in SQL GROUP BY:
SELECT channel, COUNT(*) AS orders, SUM(amount) AS revenue
FROM (
SELECT 'online' AS channel, amount FROM online_orders
UNION ALL
SELECT 'store', amount FROM store_orders
) AS all_orders
GROUP BY channel
ORDER BY channel;| channel | orders | revenue |
|---|---|---|
| online | 4 | 120.00 |
| store | 2 | 85.00 |
INTERSECT: rows in both queries
INTERSECT returns the rows that appear in both results. Which customers bought through both channels?
SELECT email FROM online_orders
INTERSECT
SELECT email FROM store_orders;| ben@birch.co |
EXCEPT: rows in the first query only
EXCEPT returns rows from the first query that do not appear in the second. Who bought online but never in the store?
SELECT email FROM online_orders
EXCEPT
SELECT email FROM store_orders
ORDER BY email;Order matters with EXCEPT: swap the two queries and you get Dev, the store-only customer. Oracle calls this operator MINUS. For the same question written as a join, see the SQL anti join guide.
Like UNION, both operators remove duplicates. Postgres and DuckDB also support INTERSECT ALL and EXCEPT ALL, which keep them. With EXCEPT ALL, each row in the second query cancels one matching row in the first:
SELECT email FROM online_orders
EXCEPT ALL
SELECT email FROM store_orders
ORDER BY email;Ben's single store row cancelled his single online row. Ana has no store rows, so both of hers stay.
Common mistakes
Different column counts
SELECT email, amount FROM online_orders
UNION ALL
SELECT email FROM store_orders;DuckDB stops with Set operations can only apply to expressions with the same number of result columns. Postgres gives a similar error. Add the missing column, or a NULL AS amount placeholder.
Columns in a different order
This one is worse, because DuckDB may run it:
SELECT order_id, email FROM online_orders
UNION ALL
SELECT email, receipt_id FROM store_orders;| order_id | |
|---|---|
| 1 | ana@acme.io |
| 2 | ben@birch.co |
| 3 | ana@acme.io |
| 4 | cara@cobalt.dev |
| ben@birch.co | 901 |
| dev@dune.studio | 902 |
DuckDB converted both columns to text so the query could run, and the store rows now have emails in the order_id column. Postgres refuses with UNION types integer and character varying cannot be matched. Match columns by position, every time. DuckDB also has UNION ALL BY NAME, which matches columns by name instead, but it is not standard SQL.
Using UNION when you need a JOIN
UNION adds rows. A JOIN adds columns. If you want a customer's online total next to their store total on the same row, that is a join (or a GROUP BY over a UNION ALL, as above). See how to join two tables.
FAQ
Is UNION ALL faster than UNION?
Usually, yes. UNION has to compare rows to find duplicates, which means sorting or hashing the whole result. UNION ALL just appends.
Does UNION remove duplicates within one table?
Yes. It de-duplicates the final combined result, so two identical rows from the same query also collapse into one.
Can I use ORDER BY in each part of a UNION?
Not directly. Put one ORDER BY after the last query. To sort or limit one part on its own, wrap it in parentheses or a subquery.
What is the difference between JOIN and UNION?
JOIN matches rows from two tables on a key and puts their columns side by side. UNION stacks rows of the same shape on top of each other.
Set operators are a favourite in SQL interview questions. You can practise UNION, INTERSECT and EXCEPT on one company's data on the practice page.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- One invite list from two tables
- Units that vanished in a UNIONAudit
- Customers who bought in January and December
- Bought in Q1, gone by Q4