UNION vs JOIN in SQL: When to Stack vs Merge

UNION and JOIN both "combine two tables", which is exactly why they get confused — but they combine in perpendicular directions. UNION stacks result sets vertically, adding more rows of the same shape. JOIN merges tables horizontally, adding more columns to each row by matching on a key. Pick by asking one question: do I need more rows, or wider rows?

What is the difference between UNION and JOIN?

UNION appends one query's rows below another's, like concatenating two spreadsheets with identical headers. JOIN pairs rows from two tables whose key values match, like VLOOKUP pulling extra columns into a sheet. The output shapes differ accordingly: UNION output is as wide as either input and up to as long as both combined; JOIN output is as wide as both inputs combined and as long as the matches dictate.

-- UNION: stack (same shape, more rows)
SELECT customer_id, email FROM us_customers
UNION ALL
SELECT customer_id, email FROM eu_customers;

-- JOIN: merge (shared key, more columns)
SELECT c.customer_id, c.email, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

What schema does each one require?

UNION demands that both SELECTs return the same number of columns with compatible types, in the same order — the column names come from the first query and don't need to match. JOIN demands almost nothing about overall shape; it only needs a join condition, typically equality on a key column present in both tables.

-- UNION: same column count/types required; pad gaps with literals
SELECT name, email, 'customer' AS source FROM customers
UNION ALL
SELECT full_name, contact_email, 'lead' FROM leads;

-- This FAILS: 2 columns vs 3 columns
-- SELECT name, email FROM customers
-- UNION SELECT full_name, contact_email, phone FROM leads;

The padding trick above — adding a literal source column — is worth memorizing: it both fixes column-count mismatches and preserves where each row came from after stacking. Also decide deliberately between UNION and UNION ALL; the deduplication difference is covered in UNION vs UNION ALL.

When do people use JOIN but need UNION?

The classic mistake: data split across sibling tables — orders_2025 and orders_2026, or one table per region — and someone tries to JOIN them to "get everything". A join on order_id between two years' tables returns only orders that somehow exist in both (usually none), or worse, a fanned-out mess if joined on customer_id. Sibling tables with the same schema are a stacking problem, not a merging one.

-- WRONG: joining sibling tables returns near-empty nonsense
SELECT *
FROM orders_2025 a
JOIN orders_2026 b ON a.order_id = b.order_id;   -- almost no matches

-- RIGHT: stack them, then analyze as one table
SELECT * FROM orders_2025
UNION ALL
SELECT * FROM orders_2026;

The reverse mistake exists too: UNION-ing a customers table with an orders table "to combine them" just interleaves incompatible rows. Different entities that share a key are a JOIN problem. The test is always the same — same kind of rows: UNION; related but different rows: JOIN.

Can I use UNION and JOIN together?

Constantly — it's the standard pattern for partitioned data. Stack the sibling tables first (usually in a CTE), then join the stacked result to dimension tables for enrichment. Stacking first keeps the join logic written once instead of once per sibling table.

-- Stack two years of orders, then enrich with customer data
WITH all_orders AS (
    SELECT order_id, customer_id, amount, order_date
    FROM orders_2025
    UNION ALL
    SELECT order_id, customer_id, amount, order_date
    FROM orders_2026
)
SELECT
    c.region,
    DATE_TRUNC('month', a.order_date) AS month,
    SUM(a.amount) AS revenue
FROM all_orders a
JOIN customers c ON c.customer_id = a.customer_id
GROUP BY c.region, DATE_TRUNC('month', a.order_date)
ORDER BY month, region;

Which is faster, UNION or JOIN?

They're not alternatives for the same task, so "faster" only matters within each: UNION ALL is nearly free (pure concatenation) while UNION pays a deduplication sort or hash over the combined rows — on large stacks that's the difference between seconds and minutes. JOIN cost depends on indexes, join algorithm, and row counts. If a query is slow, first make sure you didn't pick the wrong operator entirely; a fanned-out join produces massive intermediate results that no optimizer can save.

	UNION / UNION ALL	JOIN
Direction	Vertical (more rows)	Horizontal (more columns)
Requirement	Same column count and types	Shared key + join condition
Typical input	Sibling tables, same entity	Different entities, related by key
Output rows	Up to sum of inputs	Depends on matches (can shrink or fan out)
Main cost	Dedup (UNION only)	Matching (index/hash/sort)