SQL UNION vs UNION ALL Explained

The most common UNION ALL use case in data engineering: stacking tables that share the same schema. Regional tables, monthly partitions, or source-specific staging tables often need to be combined into one unified dataset. Since each source table has unique rows, UNION ALL is correct and faster than UNION.

-- 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;

When two data sources might contain the same rows (like overlapping date ranges from incremental loads), UNION removes the duplicates automatically. This is a quick dedup approach, though it compares every column. If you only need to dedup on specific columns, a CTE with ROW_NUMBER is more precise.

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

Sometimes you need a set of values from multiple sources for filtering. UNION deduplicates the combined set so you do not get inflated results when using it as a subquery. This pattern appears in interview questions where you need to find all customers from multiple criteria.

-- 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
);

UNION ALL can append summary rows to detail rows. This pattern is common in reporting queries where you want a totals row at the bottom. Each SELECT produces a different type of row, and UNION ALL stacks them together. Column count and types must match across all SELECTs.

-- 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;