UNION vs UNION ALL
Fast recall
Quick revision
Core answer
UNION ALL preserves every row, including duplicates, while UNION applies duplicate removal to the combined result. Use UNION ALL when duplicates are valid or the branches are known to be disjoint; use UNION only when set-like uniqueness is part of the requirement.Whether vertical combination, column compatibility, duplicate semantics, and cost are distinguished.
Using `UNION` automatically and silently losing meaningful duplicate events or paying for unnecessary deduplication.
Choose from the data contract and prove when branches are disjoint enough for `UNION ALL`.
Interview-ready guide
Interview answer
UNION and UNION ALL combine the rows produced by two or more queries. This is a vertical operation: corresponding columns occupy the same positions, so every branch must return the same column count and values with compatible types. Output column names normally come from the first query branch.
UNION ALL appends every row and preserves duplicates. UNION adds a distinct step over the combined result, so two rows that are equal across all selected columns collapse into one. It is different from a join, which combines columns from related rows horizontally.
For example, a current-customer list containing Mina and Omar and an archived list containing Omar and Asha produce four rows with UNION ALL; Omar appears twice. UNION produces three unique names. Neither is automatically correct. If the report asks for source occurrences, the duplicate may carry meaning. If it asks for the set of known names, uniqueness is required.
SELECT name FROM current_customers
UNION ALL
SELECT name FROM archived_customers;Duplicate removal generally adds work through sorting, hashing, or another engine strategy, so UNION ALL is often cheaper when duplicates are acceptable or impossible by design. Exact planning differs between PostgreSQL and SQLite. A single final ORDER BY should define presentation order for the compound result; branch ordering without branch-specific limiting does not provide a portable final-order guarantee. I state whether duplicates are data or noise, align each column deliberately, and choose the operator from that rule.
UNION vs UNION ALL
Direct Answer
Both operators append compatible query results vertically. UNION ALL preserves every row, including duplicates, while UNION applies duplicate removal to the combined result. Use UNION ALL when duplicates are valid or the branches are known to be disjoint; use UNION only when set-like uniqueness is part of the requirement.
Deep Dive & Architecture
Compound-result modes. A compound query stacks one compatible result underneath another. UNION ALL performs the stack directly. UNION presents set-like output by eliminating rows that are equal in every selected column.
Column alignment and deduplication. Each branch produces the same number of columns. The engine aligns them by position, not by matching alias text. With UNION, it then identifies duplicate combined rows using an engine-chosen method. With UNION ALL, no cross-branch identity test is required for correctness.
Combined names result. The first compound SELECT demonstrates the four-row UNION ALL stream. The final query uses UNION, removes the repeated Omar row, and sorts the three names for deterministic verification. Deduplication considers the selected name only; adding a different source column would make the rows distinct.
-- Expected result: [["Asha"],["Mina"],["Omar"]]
CREATE TABLE current_customers (name TEXT NOT NULL);
CREATE TABLE archived_customers (name TEXT NOT NULL);
INSERT INTO current_customers VALUES ('Mina'), ('Omar');
INSERT INTO archived_customers VALUES ('Omar'), ('Asha');
SELECT name FROM current_customers UNION ALL SELECT name FROM archived_customers;
SELECT name FROM current_customers
UNION
SELECT name FROM archived_customers
ORDER BY name;
Type and grain boundaries. Type coercion, text collation, and NULL equality during duplicate elimination follow engine rules. A UNION can hide an upstream duplication bug rather than fix it. Prefer UNION ALL when business rows must be preserved, and add a deliberate distinct operation only at the grain where uniqueness is actually defined.
Common Mistakes & Pitfalls
Using UNION automatically and silently losing meaningful duplicate events or paying for unnecessary deduplication.
Check your understanding
Answer these without looking back at the article.
- Why can `UNION` be more expensive than `UNION ALL`?
- Which columns determine whether two union rows are duplicates?
- How is a union different from a join?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.