UNION vs UNION ALL in SQL: what is the difference?

2 queries each return a list, and you want one combined list. UNION and UNION ALL both do that. The difference is a single word, and it is one of the most common SQL interview questions: UNION throws away duplicate rows, and UNION ALL keeps every row.

This guide uses two small order tables. The queries ran in DuckDB, and each output is exactly what it printed. It works the same way in MySQL, PostgreSQL, Snowflake and BigQuery, with one note on BigQuery marked below.

The 2 tables

An online shop and a physical store each keep their own orders. Notice that Ravi has an 800 order in both places. They are two separate orders that happen to look the same.

CREATE TABLE online_orders (order_id INTEGER, customer VARCHAR, amount INTEGER);
INSERT INTO online_orders VALUES (1, 'Asha', 1200), (2, 'Ravi', 800), (3, 'Meena', 450);

CREATE TABLE store_orders (order_id INTEGER, customer VARCHAR, amount INTEGER);
INSERT INTO store_orders VALUES (4, 'Asha', 300), (5, 'Ravi', 800), (6, 'Kiran', 2500);

1UNION ALL: stack everything

UNION ALL puts the second result under the first and keeps every row.

SELECT customer FROM online_orders
UNION ALL
SELECT customer FROM store_orders
ORDER BY customer;

customer
--------
Asha
Asha
Kiran
Meena
Ravi
Ravi
(6 rows)

6 rows, one for every order, and the names repeat because Asha and Ravi appear in both tables. That is usually what you want when the rows are events such as orders, logs or payments: each row is a real thing that happened.

2UNION: stack, then remove duplicates

UNION does the same stacking and then removes any row that is an exact copy of another.

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders
ORDER BY customer;

customer
--------
Asha
Kiran
Meena
Ravi
(4 rows)

Only 4 rows. Asha, Ravi, Meena and Kiran each appear once. UNION is the right tool when you want a list of distinct things, such as "all the customers who ordered anywhere".

3The mistake: UNION drops real rows

Now compare the two on a question where the duplicates are real. What is the total revenue across both tables? Stack the amounts, not just the names:

SELECT SUM(amount) AS revenue_with_union
FROM (
  SELECT customer, amount FROM online_orders
  UNION
  SELECT customer, amount FROM store_orders
) AS combined;

revenue_with_union
------------------
5250
(1 row)

SELECT SUM(amount) AS revenue_with_union_all
FROM (
  SELECT customer, amount FROM online_orders
  UNION ALL
  SELECT customer, amount FROM store_orders
) AS combined;

revenue_with_union_all
----------------------
6050
(1 row)

The two answers differ by 800. The first is wrong. Ravi really did place two 800 orders, one online and one in the store, but the rows ('Ravi', 800) looked identical, so UNION merged them into one and the revenue came out too low. No error appeared. Because UNION compares the selected columns only, rows that differ in the real world can look the same to it. If you had also selected order_id, the rows would have been different and nothing would be lost.

4The rules for combining queries

Both queries must follow these rules, or the database refuses:

• The same number of columns. Otherwise you get an error:

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

Binder Error: Set operations can only apply to expressions with the same number of result columns

• Compatible types in the same positions. The first column of both queries should be the same kind of thing (text with text, numbers with numbers).
• Column names come from the first query. The names from the second query are ignored.
• ORDER BY goes once, at the very end, and sorts the whole combined result, as in the queries above.

5Which one is faster?

Removing duplicates is not free. UNION has to compare every row with every other row, usually by sorting or hashing the whole result. UNION ALL just appends, so it does far less work, which matters on millions of rows. A good habit is to write UNION ALL by default and switch to UNION only when you actually need the dedupe.

6INTERSECT and EXCEPT: the other set operations

Two more operators treat results as sets, and they remove duplicates like UNION does. INTERSECT keeps rows found in both queries. EXCEPT keeps rows from the first that are not in the second.

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders
ORDER BY customer;

customer
--------
Asha
Ravi
(2 rows)

Asha and Ravi ordered in both places.

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders
ORDER BY customer;

customer
--------
Meena
(1 row)

Meena ordered online but never in the store. In Oracle the same operator is called MINUS, and BigQuery requires you to write UNION ALL or UNION DISTINCT, because it does not accept a bare UNION.

UNION or JOIN?

They solve different problems. A JOIN adds columns: it puts an order next to its customer's details. A UNION adds rows: it puts one list of orders under another. If 2 tables have the same columns and you want a longer list, use UNION ALL. If 2 tables have different columns that connect through a key, use a join.

Practise exactly this comparison with UNION vs UNION ALL counts, a free problem. If duplicate rows are your real problem, see how to remove duplicate rows in SQL.