What is the difference between UNION and UNION ALL?

Q: What is the difference between UNION and UNION ALL?

**Target Audience:** Beginners combining results from two tables for the first time, and candidates who need the exact rules and the cost. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You are organising a party and two friends each bring a guest list. You want one list. - The quick way: staple the second list under the first. Done in a second. Some people appear twice, and you may *want* that, for example to see who was invited by both friends. That is **UNION ALL**. - The careful way: staple them, then go through the who

The problem

Question presented to candidate: "We collect leads from the website and from events, in two tables. I want one combined list. Should I use UNION or UNION ALL, what exactly is the difference, and what does it cost?"

What a strong answer should cover:

Both stack the rows of two queries with the same number of columns; UNION ALL keeps every row, UNION removes duplicate rows from the combined result
Duplicates are whole rows (every column compared), they are removed within a branch too, and two NULLs count as equal for this purpose
The cost: UNION must sort or hash the whole result to find duplicates (PostgreSQL shows a HashAggregate or Unique over Append); UNION ALL just appends and can stream
Rules: column count must match, types must be compatible (PostgreSQL is strict, SQLite is not), column names come from the first branch, one ORDER BY at the end applies to the whole result
Default to UNION ALL when the branches cannot overlap or duplicates are wanted; use UNION only when you really need distinct rows
The relatives: INTERSECT and EXCEPT (and their ALL forms in PostgreSQL)

Code / implementation expected: Optional — the same two queries combined both ways, with row counts.

Schema used by the examples:

union

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners combining results from two tables for the first time, and candidates who need the exact rules and the cost. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You are organising a party and two friends each bring a guest list. You want one list.

The quick way: staple the second list under the first. Done in a second. Some people appear twice, and you may want that, for example to see who was inv

The solution is hidden so you can try first

12 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLRow counts: 4 + 4 rows give 8 with UNION ALL and 5 with UNION

SQLUNION compares whole rows: duplicates inside one branch go too, NULLs count as equal

SQLAdding a source column changes what counts as a duplicate

PostgreSQL: UNION adds a HashAggregate on top of the Append

EXPLAIN (COSTS OFF)
SELECT email, name FROM web_leads
UNION
SELECT email, name FROM event_leads;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.