JOIN vs UNION in SQL: the difference, with examples

The dataset

Three small tables, declared inline so you can paste and run them. web and store hold orders from two channels. crm holds a tier per customer.

WITH web AS (
  SELECT 'ana' AS customer, 120 AS amount
  UNION ALL SELECT 'ben', 40
  UNION ALL SELECT 'ben', 40
),
store AS (
  SELECT 'ben' AS customer, 40 AS amount
  UNION ALL SELECT 'cy', 500
),
crm AS (
  SELECT 'ana' AS customer, 'gold' AS tier
  UNION ALL SELECT 'cy', 'silver'
  UNION ALL SELECT 'dee', 'gold'
)
SELECT * FROM web;

Ben has two identical web orders on purpose. Keep the WITH block and replace the last line with each query below.

UNION: stack rows

web and store have the same columns, so they can be stacked.

SELECT * FROM web
UNION ALL
SELECT * FROM store;

Five rows: every row from both tables, duplicates included.

SELECT * FROM web
UNION DISTINCT
SELECT * FROM store;

Three rows: ana 120, ben 40, cy 500. UNION DISTINCT removes every duplicate row, including the ones that were already inside web. Ben’s three 40-unit orders across the two channels become one.

That is the trap. If those were three real orders, UNION DISTINCT just deleted two of them, and revenue fell from 740 to 660 without an error. Use UNION ALL unless you can say why duplicates must go. It is also cheaper, because the database does not have to compare rows.

UNION ALL vs UNION DISTINCT in BigQuery

In BigQuery you must write one of the two. A bare UNION is a syntax error:

Syntax error: Expected keyword ALL or keyword
DISTINCT but got keyword SELECT

Many other databases accept a bare UNION and treat it as UNION DISTINCT. So a query moved to BigQuery from elsewhere needs the word added, and the right word to add for a bare UNION is DISTINCT if you want the same result as before.

What UNION requires

• The same number of columns. Otherwise: “Queries in UNION ALL have mismatched column count”.
• Compatible types in each position. Otherwise: “Column 1 in UNION ALL has incompatible types: STRING, INT64”.
• Columns are matched by position, not by name. If one query selects customer, amount and the other amount, customer, BigQuery either raises the type error above or, when the types happen to match, puts values in the wrong columns without complaint.

To match by name instead, BigQuery accepts BY NAME:

SELECT customer, amount FROM web
UNION ALL BY NAME
SELECT amount, customer FROM store;

The result takes its column names from the first query.

JOIN: add columns

crm describes customers, not orders, so it cannot be stacked under web. It is joined to it, on the column both share.

SELECT w.customer, w.amount, c.tier
FROM web AS w
JOIN crm AS c USING (customer);

One row: ana, 120, gold. An inner join keeps only the rows that match on both sides. Ben is not in crm, so both his orders are gone.

The type of join decides what happens to rows without a match:

SELECT w.customer, w.amount, c.tier
FROM web AS w
LEFT JOIN crm AS c USING (customer);

Three rows: ana with gold, and ben twice with NULL in tier. For a report on orders this is usually the right join. An inner join would have dropped Ben’s revenue because a lookup table was incomplete.

RIGHT JOIN is a left join with the tables swapped, and CROSS JOIN pairs every row with every row, with no key at all.

The mistake JOIN makes: fan-out

A join repeats a row once for each match on the other side. Join store to web on customer and sum the store amounts:

SELECT SUM(s.amount) AS store_revenue
FROM store AS s
JOIN web AS w USING (customer);
-- 80

Ben’s single store order of 40 matched his two web orders, so it appears twice and the sum is 80, not 40. No error, no warning, and a number that looks reasonable.

This is the most common way a join produces a wrong total. Before you sum across a join, check that the key is unique on the side you are joining to:

SELECT customer, COUNT(*) AS n
FROM web
GROUP BY customer
HAVING COUNT(*) > 1;

If that returns rows, aggregate that table to one row per key first, then join. Unique keys are what primary and foreign keys describe.

When to use which

• The tables have the same columns and mean the same thing (two months, two regions, two channels): UNION ALL.
• One table describes the rows of the other (orders and customers, sessions and campaigns): JOIN.
• You want rows in one table that are, or are not, in another: INTERSECT DISTINCT and EXCEPT DISTINCT. With the dataset above, the first returns ben 40 and the second ana 120.

A frequent real case needs both. Stack the channels first, then add customer details:

SELECT o.channel, o.customer, o.amount, c.tier
FROM (
  SELECT 'web' AS channel, * FROM web
  UNION ALL
  SELECT 'store', * FROM store
) AS o
LEFT JOIN crm AS c USING (customer);

Adding a channel column before the union keeps the origin of every row, which you will want the first time someone asks where a number came from. For readability on longer queries, put each step in a common table expression.

Performance, briefly

• UNION ALL only appends. UNION DISTINCT has to deduplicate the whole result.
• A join costs more the more rows it has to match. Filter each table in a WHERE clause before joining, and select only the columns you need.
• In BigQuery, a join on a partitioned table with a filter on the partition column scans less data.

Why the join belongs in one place

Both silent failures on this page, the UNION DISTINCT that removed real orders and the join that counted one order twice, produce a plausible number. Nobody notices at the time. They notice when two reports disagree, and by then each report has its own copy of the join.

The practical fix is to write the join once, by the person who knows which key is unique, and have reports read the result. In OWOX Data Marts that is what a Data Mart and its relationships are for: your SQL defines each dataset, you define how two Data Marts join, and reports in a spreadsheet or dashboard use that definition. OWOX does not guess the key or write the join for you. It keeps the one you wrote from being rewritten five different ways.

A model drawn once looks like this: each Data Mart is a card, and each line is a join somebody decided on.

Google BigQueryComprehensive Guide to BigQuery Aggregate Functions for 2025April 18, 2024→Google BigQueryBigQuery conversion functions: every type-to-type conversionOctober 6, 2026→Google BigQueryBigQuery DML Commands: A Complete Guide for Data AnalystsMarch 14, 2024→

See all articles →