SQL UNION vs UNION ALL in Postgres, with examples
UNION removes duplicate rows; UNION ALL keeps them and is faster. Column rules, ORDER BY and LIMIT placement, INTERSECT and EXCEPT in Postgres.
UNION puts the rows of one query underneath the rows of another and returns them as a single result. UNION removes duplicate rows. UNION ALL keeps every row. That one word decides both the answer you get and how much work Postgres does to produce it.
This article uses PostgreSQL, which is what every Scribase project runs. The examples reuse the customers and orders tables from the SQL subquery article and add one more table. You can create all of it on a disposable database branch.
The tables
CREATE TABLE customers (
id bigint PRIMARY KEY,
email text NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id bigint PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers (id),
status text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
total_cents integer NOT NULL CHECK (total_cents >= 0),
placed_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE newsletter_signups (
email text NOT NULL,
signed_up_at timestamptz NOT NULL DEFAULT now()
);
Some people are customers, some only signed up for the newsletter, and some are both. That overlap is what makes the difference between UNION and UNION ALL visible.
UNION removes duplicates
SELECT email FROM customers
UNION
SELECT email FROM newsletter_signups;
Each email appears once, even if the person is in both tables, and even if the same address signed up for the newsletter twice. UNION compares whole rows, so with one column it behaves like SELECT DISTINCT over the combined list.
Two details surprise people:
- Duplicates inside a single branch are removed too, not only duplicates between the two branches.
- For this comparison two
NULLvalues count as the same, so severalNULLrows collapse into one.
UNION ALL keeps everything
SELECT email, 'customer' AS source FROM customers
UNION ALL
SELECT email, 'newsletter' AS source FROM newsletter_signups;
Every row from both queries comes back. Adding a literal source column is a common pattern: it tells you where each row came from, which a plain UNION would make harder, because the rows would no longer be identical and nothing would be removed anyway.
Use UNION ALL when:
- the branches cannot overlap (paid orders and cancelled orders, this month and last month);
- you want duplicates, for example to count them;
- you are going to aggregate the combined rows yourself.
SELECT email, count(*) AS appearances
FROM (
SELECT email FROM customers
UNION ALL
SELECT email FROM newsletter_signups
) AS everyone
GROUP BY email
HAVING count(*) > 1;
That query finds the addresses that appear more than once. With UNION instead of UNION ALL it would return nothing, because the duplicates would be gone before count(*) saw them. This is the most common UNION bug in reports: totals that are quietly too low.
The column rules
Both queries must return the same number of columns, and each pair of columns must have compatible types. Columns are matched by position, not by name.
-- Wrong: the columns line up by position, so emails land under "id".
SELECT id, email FROM customers
UNION ALL
SELECT email, signed_up_at FROM newsletter_signups;
Postgres rejects that statement with UNION types bigint and text cannot be matched. It is less kind when the types happen to be compatible: swap two text columns and you get a valid result with the data in the wrong columns.
The output column names come from the first query. If the branches have different numbers of columns, pad the shorter one with a typed NULL:
SELECT email, created_at AS seen_at, id AS customer_id
FROM customers
UNION ALL
SELECT email, signed_up_at, NULL::bigint
FROM newsletter_signups;
ORDER BY and LIMIT belong to the whole result
An ORDER BY at the end sorts the combined rows, not the last query. It can only use output column names (from the first query) or positions.
SELECT email, created_at AS seen_at FROM customers
UNION ALL
SELECT email, signed_up_at FROM newsletter_signups
ORDER BY seen_at DESC
LIMIT 20;
To sort or limit one branch on its own, wrap it in parentheses:
(SELECT email, created_at AS seen_at
FROM customers
ORDER BY created_at DESC
LIMIT 5)
UNION ALL
(SELECT email, signed_up_at
FROM newsletter_signups
ORDER BY signed_up_at DESC
LIMIT 5);
Without a final ORDER BY, the order of the combined rows is not guaranteed. UNION ALL usually returns the first branch and then the second, but that is an implementation habit and not a promise. See ORDER BY DESC for stable sorting.
INTERSECT and EXCEPT
The same family has two more members. INTERSECT returns rows present in both queries. EXCEPT returns rows from the first query that are not in the second.
-- Customers who are also on the newsletter.
SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_signups;
-- Newsletter readers who have never become customers.
SELECT email FROM newsletter_signups
EXCEPT
SELECT email FROM customers;
Both remove duplicates unless you add ALL. When you chain several operators, INTERSECT binds more tightly than UNION and EXCEPT, so a UNION b INTERSECT c means a UNION (b INTERSECT c). Use parentheses and nobody has to remember that.
UNION is slower than UNION ALL, and that is fine when you need it
UNION ALL is an append: Postgres runs each branch and hands the rows on. UNION has to find the duplicates as well, which means hashing or sorting every combined row. On a few thousand rows you will not notice. On millions of wide rows it can be the most expensive step in the plan.
So the rule is short: write UNION ALL unless you need duplicates removed, and when you do need them removed, write UNION on purpose. If the branches cannot overlap, UNION only buys you a slower query. EXPLAIN (ANALYZE, BUFFERS) shows the difference as an Append node on its own, or an Append under a HashAggregate or a Sort and Unique.
A related habit: a UNION of two queries on the same table with different WHERE clauses is often just one query with OR or IN. Sometimes the UNION ALL form is faster, because each branch can use its own index. Measure it instead of guessing.
Checking the result on a Scribase branch
A UNION that should have been UNION ALL does not raise an error. It returns a plausible, smaller number. The way to catch it is to run both forms against realistic data and compare the row counts, and realistic data is exactly what you should not experiment on in production.
Scribase gives every project copy-on-write Postgres branches. A branch is a full, writable Postgres that starts as a copy of another database without copying every page first, and writes on the branch never reach the parent. Branches and preview environments are included in the project price, on the free project too.
- Create a preview environment. The default data mode is
sanitized: a copy of the parent with configured columns anonymized, so the emails in these examples are masked but the duplicates and overlaps are still there. Useschema_onlyto load your own fixtures. - Run the query with
UNION, then withUNION ALL, and comparecount(*). If the counts differ, decide which one the report means. - If you added an index to make a branch of the union cheap, review
scribase branch diffand dry-run the merge. Merges carry schema changes, not rows. - Delete the preview, or let its TTL remove it.
The full loop is in branch per task, and the mechanics are on the Postgres branching page. For a quick local try, with no account:
scribase branch new try-union
psql "$(scribase branch url try-union)"
scribase branch delete try-union
Common mistakes
- Writing
UNIONout of habit and getting totals that are too low. - Writing
UNION ALLwhere two branches overlap and getting totals that are too high. - Lining columns up by name in your head while Postgres lines them up by position.
- Putting
ORDER BYafter the first query and wondering why it is a syntax error. It goes at the very end, or inside parentheses. - Expecting the combined rows in a particular order without an
ORDER BY.
What to read next
SQL subquery and common table expressions cover the other ways to combine queries, and a CTE is a good place to give a long UNION a name. ORDER BY DESC covers sorting the combined result. The product guides are in the Scribase guides.
To try the examples on a hosted project, start at scribase.com. One project is free, with no card and no time limit. A temporary project via npx scribase init --temp needs no account.