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 subsets
SELECT order_id, status, profit, 'US' AS region
FROM orders WHERE region = 'US'
UNION ALL
SELECT order_id, status, profit, 'EU' AS region
FROM orders WHERE region = 'EU'
UNION ALL
SELECT order_id, status, profit, 'APAC' AS region
FROM orders WHERE region = '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 user_id, email, signup_date
FROM users
WHERE user_id <= 294
UNION
SELECT user_id, email, signup_date
FROM users
WHERE user_id BETWEEN 197 AND 488;
-- Rows appearing in both exports 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 users who made a transaction OR triggered an event
SELECT user_id FROM transactions
UNION
SELECT user_id FROM event_data;

-- Use as a filter
SELECT * FROM users
WHERE user_id IN (
  SELECT user_id FROM transactions
  UNION
  SELECT user_id FROM event_data
);

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,
  emp_name,
  salary
FROM employees
UNION ALL
SELECT
  'TOTAL' AS department,
  NULL AS emp_name,
  SUM(salary) AS salary
FROM employees;