What is the difference between UNION and UNION ALL, and when would you deliberately want the duplicates?
The difference is one word, and that word decides whether you keep the duplicate rows.
UNION ALL keeps every row from both sides. UNION throws away the rows that appear twice.
CREATE TABLE orders_2025 (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
CREATE TABLE orders_2026 (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO orders_2025 (id, customer_id, amount) VALUES
(1, 1, 250.00),
(2, 1, 120.00);
INSERT INTO orders_2026 (id, customer_id, amount) VALUES
(3, 2, 900.00),
(4, 1, 75.00);
-- The same customer in both years. The two rows are not the same,
-- so both of them stay.
SELECT customer_id, amount FROM orders_2025
UNION ALL
SELECT customer_id, amount FROM orders_2026
ORDER BY customer_id;
-- Exactly the same rows in both. The duplicates are removed.
SELECT customer_id, amount FROM orders_2025
UNION
SELECT customer_id, amount FROM orders_2026
ORDER BY customer_id;
So why does it matter? Two reasons.
The first one is speed. The UNION has to find the duplicates, and that means it has to sort or hash everything, and that costs time and memory. The UNION ALL has to do nothing except put the two sets together, so it is much faster on a large amount of data.
The second one is that the two give different answers, so the choice is a decision about what you mean and not only about speed.
So when would you deliberately want the duplicates? Three cases.
The first one is when the rows on the two sides are genuinely different things. If you are combining this year's orders with last year's orders, then the same customer appearing in both is not a duplicate, it is two real orders. Using UNION there would quietly throw away real data.
The second one is when you know the two sets cannot overlap, such as the orders from the north region and the orders from the south region. In that case the UNION does the same work as the UNION ALL and takes longer for it.
The third one is when you are counting. The number of rows that come back from a UNION ALL is the number you want, and a UNION gives you a smaller number that you then have to work out the meaning of.
-- How many orders in total across both years.
SELECT COUNT(*) AS total_orders FROM (
SELECT id FROM orders_2025
UNION ALL
SELECT id FROM orders_2026
);
So the rule to remember is this. Use UNION ALL unless you have a reason to remove the duplicates, because it is faster and because it is what you usually mean.
The last question in this topic looks at combining sets that are not the same shape.