# 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.

Source: https://scribase.com/blog/sql-union

By Scribase, 2026-11-18.

`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](/blog/sql-subquery) article and add one more table. You can create all of it on a disposable database branch.

## The tables

```sql
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

```sql
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 `NULL` values count as the same, so several `NULL` rows collapse into one.

## UNION ALL keeps everything

```sql
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.

```sql
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.

```sql
-- 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`:

```sql
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.

```sql
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:

```sql
(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](/blog/desc-order) 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.

```sql
-- 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.

1. 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. Use `schema_only` to load your own fixtures.
2. Run the query with `UNION`, then with `UNION ALL`, and compare `count(*)`. If the counts differ, decide which one the report means.
3. If you added an index to make a branch of the union cheap, review `scribase branch diff` and dry-run the merge. Merges carry schema changes, not rows.
4. Delete the preview, or let its TTL remove it.

The full loop is in [branch per task](https://docs.scribase.com/docs/agents/branch-per-task), and the mechanics are on the [Postgres branching](/postgres-branching) page. For a quick local try, with no account:

```sh
scribase branch new try-union
psql "$(scribase branch url try-union)"
scribase branch delete try-union
```

## Common mistakes

- Writing `UNION` out of habit and getting totals that are too low.
- Writing `UNION ALL` where 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 BY` after 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](/blog/sql-subquery) and [common table expressions](/blog/common-table-expression) cover the other ways to combine queries, and a CTE is a good place to give a long `UNION` a name. [ORDER BY DESC](/blog/desc-order) covers sorting the combined result. The product guides are in the [Scribase guides](/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.