SQL UNION: UNION vs UNION ALL vs JOIN

UNION combines the results of two or more SELECT statements into a single result set and removes duplicate rows. Both queries must return the same number of columns, and the column types must be compatible. Column names come from the first SELECT statement.

-- Combine active and archived customers, remove duplicates
SELECT customer_id, name, email
FROM active_customers

UNION

SELECT customer_id, name, email
FROM archived_customers;

-- If a customer appears in both tables with identical
-- values in all three columns, only one row is returned

UNION ALL combines result sets without removing duplicates. It is faster than UNION because it skips the deduplication step. Use UNION ALL when you know the datasets do not overlap, or when duplicates carry meaning (like transaction logs from multiple systems).

-- Combine transactions from multiple regional databases
SELECT txn_id, amount, txn_date, 'us_east' AS region
FROM us_east.transactions

UNION ALL

SELECT txn_id, amount, txn_date, 'us_west' AS region
FROM us_west.transactions

UNION ALL

SELECT txn_id, amount, txn_date, 'eu' AS region
FROM eu.transactions;

-- All rows preserved; UNION ALL is the correct choice
-- because txn_id is unique within each region

When combining tables with different column structures, you can use NULL or constant values to fill missing columns. Each SELECT must still return the same number of columns with compatible types. This pattern is common when consolidating data from systems that store similar information in different schemas.

-- Combine contact info from two differently structured tables
SELECT
  employee_id AS person_id,
  full_name AS name,
  work_email AS email,
  'employee' AS source
FROM employees

UNION ALL

SELECT
  contractor_id AS person_id,
  contractor_name AS name,
  NULL AS email,        -- contractors table has no email column
  'contractor' AS source
FROM contractors;

Many data warehouses partition large tables by time period. Querying across partitions requires UNION ALL to reassemble the full dataset. This is a real-world pattern you will encounter in Snowflake, BigQuery, and Redshift environments where historical data lives in separate tables.

-- Query across yearly partitions
SELECT order_id, customer_id, amount, order_date
FROM orders_2023

UNION ALL

SELECT order_id, customer_id, amount, order_date
FROM orders_2024

UNION ALL

SELECT order_id, customer_id, amount, order_date
FROM orders_2025
ORDER BY order_date DESC
LIMIT 1000;

-- ORDER BY and LIMIT apply to the entire UNION result
-- Wrap in parentheses if your DB requires it

You can wrap a UNION in a subquery and aggregate the combined result. This is useful for producing summary statistics across multiple sources. The UNION happens first (inside the subquery), then the outer query aggregates the combined rows.

-- Total revenue across all regional databases
SELECT
  region,
  SUM(amount) AS total_revenue,
  COUNT(*) AS transaction_count
FROM (
  SELECT amount, 'North' AS region FROM north_sales
  UNION ALL
  SELECT amount, 'South' AS region FROM south_sales
  UNION ALL
  SELECT amount, 'West' AS region FROM west_sales
) combined
GROUP BY region
ORDER BY total_revenue DESC;