UNION vs UNION ALL in SQL: Duplicates and Performance

UNION and UNION ALL both stack the rows of one query on top of another. The difference is a single step: UNION then removes duplicate rows from the combined result, and UNION ALL doesn’t. That extra step changes both the answer (fewer rows) and the cost (PostgreSQL has to sort or hash everything to find the duplicates).

So the interview answer is: use UNION ALL by default, and use UNION only when you actually want distinct rows and duplicates are possible. The details below are where candidates lose points, particularly the idea that UNION only removes rows that appear “in both” queries.

Quick comparison

Both require the same number of columns in each query, with compatible types in each position. Column names come from the first query.

What UNION actually removes

Two mailing sources, each with some mess: Ben is on the newsletter twice, Ben is also a customer, and both tables have a NULL email.

CREATE TABLE newsletter (email TEXT);
CREATE TABLE customers  (email TEXT);
INSERT INTO newsletter VALUES ('asha@example.com'), ('ben@example.com'), ('ben@example.com'), (NULL);
INSERT INTO customers  VALUES ('ben@example.com'), ('chen@example.com'), (NULL);

UNION ALL returns all seven rows, in the order the branches produce them (no order is guaranteed without ORDER BY):

SELECT email FROM newsletter
UNION ALL
SELECT email FROM customers;

asha@example.com
ben@example.com
ben@example.com
NULL
ben@example.com
chen@example.com
NULL

UNION returns four:

SELECT email FROM newsletter
UNION
SELECT email FROM customers
ORDER BY email;

NULL
asha@example.com
ben@example.com
chen@example.com

These were run in SQLite with Python’s sqlite3 module; PostgreSQL returns the same rows, except that it sorts NULL last in ascending order where SQLite sorts it first.

Two details in that output trip people up. First, the duplicate Ben inside the newsletter table was removed too, even though both copies came from the same branch. UNION deduplicates the whole combined result; it isn’t “rows from B that aren’t already in A”. Second, the two NULLs became one. In a WHERE clause NULL = NULL isn’t true, but set operations (like DISTINCT and GROUP BY) treat NULLs as not distinct from each other.

Deduplication compares entire rows. Add a column that differs and the “duplicates” come back:

SELECT email, 'newsletter' AS source FROM newsletter
UNION
SELECT email, 'customer' FROM customers
ORDER BY email, source;

email            | source
NULL             | customer
NULL             | newsletter
asha@example.com | newsletter
ben@example.com  | customer
ben@example.com  | newsletter
chen@example.com | customer

Ben now appears twice, because (ben, customer) and (ben, newsletter) are different rows. If you want one row per email with a list of sources, that’s a GROUP BY email over a UNION ALL, not a UNION.

The aggregation trap

Here’s the bug that makes UNION dangerous rather than just slow. Total all payments across two tables, where a card payment and a bank payment happen to be the same amount:

CREATE TABLE card_payments (amount INTEGER);
CREATE TABLE bank_payments (amount INTEGER);
INSERT INTO card_payments VALUES (50), (20);
INSERT INTO bank_payments VALUES (50), (30);

SELECT SUM(amount) AS total
FROM (SELECT amount FROM card_payments
      UNION
      SELECT amount FROM bank_payments) AS p;

total
100

The real total is 150. UNION saw two rows containing 50 and kept one, because a row with only an amount has no identity. With UNION ALL the query returns 150. The same thing happens with COUNT(*). Any time the combined rows feed an aggregate, two separate events that look alike must both survive, so the answer is UNION ALL. If the rows carry a primary key or an (source, id) pair, UNION wouldn’t collapse them, but then it has nothing to remove either and you’ve paid for a sort for nothing.

What the extra step costs

To find duplicates, the database has to compare every row against every other. PostgreSQL does it by hashing all rows (a HashAggregate node) or by sorting them and dropping neighbours that are equal (Sort followed by Unique). Either way it has to read both inputs completely before it can return the first row, and a large sort can spill to disk when it doesn’t fit in work_mem. SQLite’s EXPLAIN QUERY PLAN shows the same idea: USE TEMP B-TREE FOR ORDER BY on both branches of a UNION, and a plain COMPOUND QUERY with no temporary structure for UNION ALL.

UNION ALL is an Append in PostgreSQL. It emits rows from the first branch, then the second, without holding anything. That has a practical bonus: with LIMIT 10 on top, PostgreSQL can stop as soon as it has ten rows, possibly without running the second branch at all. A UNION can’t stop early.

When the branches can’t overlap by construction (different date ranges, different status values, rows from two partitions), UNION does a full deduplication pass that can never remove a row. Write UNION ALL and, if it helps the next reader, add a comment saying why duplicates are impossible.

Rules both operators share

These come up as follow-up questions, and they apply to UNION, UNION ALL, INTERSECT and EXCEPT alike.

• Column count must match. SQLite reports SELECTs to the left and right of UNION do not have the same number of result columns; PostgreSQL says each UNION query must have the same number of columns.
• Types must be compatible by position. PostgreSQL resolves a common type for each column and rejects pairs it can’t reconcile, with an error like UNION types integer and text cannot be matched. SQLite is permissive and will happily mix 1 and 'a' in one column, so don’t rely on it to catch this.
• Names come from the first branch. Aliases in later branches are ignored for the output.
• ORDER BY and LIMIT at the end apply to the whole result. To limit a single branch in PostgreSQL, wrap it in parentheses: (SELECT ... ORDER BY created_at DESC LIMIT 5) UNION ALL (SELECT ...).
• Precedence. INTERSECT binds tighter than UNION and EXCEPT. Add parentheses when you mix them, for your reader’s sake if not the parser’s.

When UNION is the right choice

Use UNION when distinct rows are the point and duplicates are genuinely possible: building one list of unique email addresses from several sources, or the set of user ids that touched either of two features. Even then, compare it with alternatives. SELECT DISTINCT over a UNION ALL is equivalent and makes the intent explicit. If the question is “users who did A or B”, a single query with WHERE ... OR ... or an EXISTS may avoid combining sets at all.

Interview practice: the shrinking revenue report

A finance query combines card_payments and bank_payments with UNION and sums the amounts. Its total is lower than the sum of the two tables queried separately. A colleague suggests adding payment_date to both selects, which brings the total closer but still not right. What’s going on, and what’s the fix?

Answer

UNION removes duplicate rows from the combined result. Rows containing only an amount are equal whenever amounts match, so separate payments of the same value collapse into one and the sum drops. Adding payment_date makes rows more distinct, which is why the total improves, but two payments of the same amount on the same day still collapse. The approach is wrong rather than incomplete: payments are events, and every event must be counted. Use UNION ALL, which never removes rows:

SELECT SUM(amount) AS total
FROM (SELECT amount FROM card_payments
      UNION ALL
      SELECT amount FROM bank_payments) AS p;   -- 150 on the sample data

A good answer also mentions that UNION ALL is cheaper here (no sort or hash) and that adding a source column is a useful way to keep the rows traceable, not a way to make UNION safe.

Further reading

• SQL interview questions and the SQL multiple-choice quiz
• SQL count star versus count column
• SQL null and three-valued logic
• PostgreSQL documentation: combining queries