SQL UNION vs UNION ALL: Differences, Examples and Performance Tested

Both operators stack the results of two queries on top of each other, so SQL UNION vs UNION ALL might look like a minor choice. It is not. One extra word changes both your result and how hard the database has to work.

We ran every query here in SQLite 3.50, and the results and timings shown come from those runs. The basic behaviour is standard SQL across major databases. Timings, however, will differ on your own database, data and hardware, so treat ours as an illustration.

The short answer

UNION combines two result sets and returns only distinct rows. UNION ALL combines them and returns everything, including duplicates. That is the whole difference, and the rest of this article is about why it matters.

UNION vs UNION ALL side by side

The sample data

Imagine a shop that keeps online and in-store orders in separate tables. Note that Asha has two identical online orders, and one identical order in store.

CREATE TABLE online_orders (customer TEXT, total INT);
CREATE TABLE store_orders (customer TEXT, total INT);

INSERT INTO online_orders VALUES ('Asha',100), ('Ben',200), ('Asha',100);
INSERT INTO store_orders VALUES ('Asha',100), ('Dev',300);

See the difference

SELECT customer, total FROM online_orders
UNION
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Dev  | 300

SELECT customer, total FROM online_orders
UNION ALL
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Asha | 100
-- Asha | 100
-- Dev  | 300

Five input rows became three with UNION, and all five stayed with UNION ALL. Two things to notice:

• UNION removed duplicates from the whole result, including those within the first table. It is not only comparing across tables.
• A row counts as a duplicate only when every selected column matches.

When you truly want a distinct list, UNION is exactly right. This gives every customer who bought through either channel:

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

The wrong-total bug

This is the reason the choice really matters. Suppose you want total revenue. The orders above add up to 800. See what each operator gives:

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION
  SELECT total FROM store_orders
);
-- 600

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION ALL
  SELECT total FROM store_orders
);
-- 800

The UNION version is wrong by 200. Because we selected only the total column, three separate 100-rupee orders looked like duplicates and collapsed into one. There is no error message, and 600 looks like a perfectly believable number. This type of quiet bug can sit in a finance report for months.

Rules both must follow

Both operators have the same requirements:

1. Each SELECT must return the same number of columns.
2. Columns are matched by position, not by name, so keep them in the same order.
3. Data types in each position must be compatible.
4. Result column names come from the first SELECT.
5. ORDER BY goes at the very end, and it applies to the whole result.

Breaking the first rule gives an error. Here is the message from SQLite:

SELECT customer, total FROM online_orders
UNION
SELECT customer FROM store_orders;

-- SELECTs to the left and right of UNION do not have
-- the same number of result columns

The second rule is more dangerous, because it can fail without an error. If you swap two columns of the same type, such as first_name and last_name, the query runs and the data comes out wrong. SQLite is also relaxed about types, while databases such as PostgreSQL are stricter and may require an explicit CAST.

Label the source

A very practical trick is to add a constant column showing where each row came from. Because the label differs, rows from different tables can no longer be treated as duplicates of each other.

SELECT customer, total, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, total, 'store' FROM store_orders
ORDER BY customer, channel;

-- Asha | 100 | online
-- Asha | 100 | online
-- Asha | 100 | store
-- Ben  | 200 | online
-- Dev  | 300 | store

Once your data has a label, you can group it to report sales by channel, using GROUP BY. If you need to combine several steps, wrap the union in a CTE to keep it readable.

Performance: what happened in our test

To remove duplicates, the database has to compare rows, typically by sorting or hashing them. UNION ALL can skip this step. We created two tables with 500,000 rows each, containing random numbers from 0 to 50,000, so there were plenty of duplicates. We ran each query several times and kept the best time.

First, we counted the results, so that the time spent sending rows back to our program did not affect the comparison:

The huge gap is partly because SQLite can work out that count without reading every row, so please do not read that as “thousands of times faster”. It does show the core idea, though: UNION had to do real work and UNION ALL did not.

Then we fetched all the rows into Python, and the result flipped:

Here, UNION ALL took longer because it delivered twenty times as many rows. Deduplication is not what slowed it. Handling a million rows was. So the honest rule is that UNION ALL is cheaper for the database to compute, but the size of the result matters too. Many charts online reduce this to “UNION ALL is always faster”, and our test shows why it is not that simple.

Do not choose based on speed alone, either. Pick the operator that gives the correct answer first, then tune. If a query is slow, read our guide to SQL query optimization.

When to use each

Use UNION ALL when:

• You are merging data from similar tables, such as monthly partitions.
• You will calculate totals or counts afterwards.
• You know the inputs cannot overlap.
• Duplicates are meaningful, as in logs or audit records.

Use UNION when:

• You need a unique list, such as every email address across two systems.
• Overlapping sources contain the same fact more than once.

Treat UNION ALL as the default, and use UNION deliberately. Adding a WHERE clause to each branch, to filter early, helps both.

INTERSECT and EXCEPT

Two related set operators answer different questions. Both follow the same column rules:

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders;
-- Asha  (bought in both channels)

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders;
-- Ben   (online only)

Oracle has traditionally used MINUS rather than EXCEPT, and support for these operators varies a little by database and version.

UNION vs JOIN

These are often confused, but they combine data in different directions:

Common mistakes

• Using UNION by habit. It may cost you speed, or remove valid rows.
• Selecting too few columns. Leaving out an ID makes distinct rows look identical.
• Mismatching column order. Values can land in the wrong column without any error.
• Adding ORDER BY to each branch. Sort once, at the end.
• Using UNION to patch a bad join. If duplicates appear because of a join, fix the join.
• Assuming result order. Without ORDER BY, row order is not guaranteed for either operator.

Final thoughts

Ask one question each time: are identical rows the same fact, or separate facts? If they are separate, or if overlap is impossible, use UNION ALL. If you need a unique list, use UNION. To check your own reports, search your code for UNION near a SUM or COUNT, and confirm that those totals really are right.

Frequently asked questions

Keep learning