SQL UNION vs UNION ALL

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

Syntax

Both operators slot between SELECTs with a strict contract: same column count, compatible types, same positional order. The first SELECT's column names win. Skip that rule once and you'll get an error loud enough to spot. Skip the UNION vs UNION ALL choice and your pipeline just runs slower every night until somebody notices.

-- UNION: removes duplicates
SELECT column1, column2 FROM table_a
UNION
SELECT column1, column2 FROM table_b;

-- UNION ALL: keeps all rows
SELECT column1, column2 FROM table_a
UNION ALL
SELECT column1, column2 FROM table_b;

-- Multiple UNIONs (can mix UNION and UNION ALL)
SELECT id, name FROM customers_2023
UNION ALL
SELECT id, name FROM customers_2024
UNION ALL
SELECT id, name FROM customers_2025;

UNION vs UNION ALL Comparison

Every difference flows from the deduplication step. UNION pays the cost; UNION ALL does not.

Concrete Example

-- Table A: (1, 'Alice'), (2, 'Bob')
-- Table B: (2, 'Bob'), (3, 'Carol')

-- UNION result: 3 rows
-- (1, 'Alice'), (2, 'Bob'), (3, 'Carol')
-- Bob appears once because UNION deduplicates

-- UNION ALL result: 4 rows
-- (1, 'Alice'), (2, 'Bob'), (2, 'Bob'), (3, 'Carol')
-- Bob appears twice, once from each table

UNION compares entire rows to detect duplicates. If even one column differs (like a timestamp), the rows are considered distinct and both are kept. This is why UNION sometimes fails to deduplicate the way people expect: it does exact row matching, not key-based matching.

Performance Implications

The performance gap between UNION and UNION ALL grows linearly with data volume. For small result sets (hundreds of rows), the difference is negligible. For millions of rows, it is substantial.

Common Patterns

Four patterns that cover the most common UNION and UNION ALL scenarios in both interviews and production pipelines.

-- Combine regional order tables
SELECT order_id, customer_id, amount, 'US' AS region
FROM orders_us
UNION ALL
SELECT order_id, customer_id, amount, 'EU' AS region
FROM orders_eu
UNION ALL
SELECT order_id, customer_id, amount, 'APAC' AS region
FROM orders_apac;

-- Two overlapping exports, remove exact duplicates
SELECT customer_id, email, signup_date
FROM export_jan
UNION
SELECT customer_id, email, signup_date
FROM export_feb;
-- Rows appearing in both months are kept only once

-- All customers who placed an order OR submitted a support ticket
SELECT customer_id FROM orders
UNION
SELECT customer_id FROM support_tickets;

-- Use as a filter
SELECT * FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders
  UNION
  SELECT customer_id FROM support_tickets
);

-- Detail rows plus a summary row
SELECT
  department,
  employee_name,
  salary
FROM employees
UNION ALL
SELECT
  'TOTAL' AS department,
  NULL AS employee_name,
  SUM(salary) AS salary
FROM employees;

Related Set Operators: INTERSECT and EXCEPT

SQL has four set operators. UNION and UNION ALL combine sets. INTERSECT finds rows present in both. EXCEPT (MINUS in Oracle) finds rows present in the first but not the second. All four require matching column counts and types.

-- INTERSECT: customers who ordered AND returned something
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM returns;

-- EXCEPT: customers who ordered but never returned
SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM returns;

-- Both INTERSECT and EXCEPT deduplicate by default
-- INTERSECT ALL and EXCEPT ALL preserve duplicates (PostgreSQL)

Interviewers sometimes ask about all four set operators in one question. Knowing INTERSECT and EXCEPT alongside UNION shows breadth. The main gotcha: EXCEPT is not commutative. A EXCEPT B is not the same as B EXCEPT A.

4 UNION Interview Questions

These questions test set operation knowledge and performance awareness. Each includes the interviewer's intent and a recommended approach.

UNION vs UNION ALL FAQ

Related Guides