---
title: "JOIN vs UNION in SQL: the difference, with examples"
canonical: "https://www.owox.com/blog/articles/bigquery-join-and-union-difference"
updated: "2026-10-12"
---

# JOIN vs UNION in SQL: the difference, with examples

JOIN puts tables side by side and adds columns, matching rows on a key. UNION stacks tables on top of each other and adds rows. This guide runs one small dataset through both, covers UNION ALL and UNION DISTINCT in BigQuery, and shows the two mistakes that silently change a total.

[Google BigQuery](/blog/topics/bigquery) · Updated October 6, 2026 · [Alyona Samovar](/team/alyona-samovar) · [Vadym Kramarenko](/team/vadym-kramarenko) · 6 min read

![JOIN vs UNION in SQL: the difference, with examples](https://cdn.owox.ai/www/webflow/696a4df5f746bb4acec0efae_JOIN-vs.-UNION-IN-BIGQUERY-SQL.png/public)

**JOIN combines tables side by side: it adds columns, matching rows on a key. UNION combines tables top to bottom: it adds rows, and the tables need the same columns.** Use JOIN when the tables describe different things about the same entity. Use UNION when they hold the same kind of record from different places.

|  | JOIN | UNION |
| --- | --- | --- |
| Combines | horizontally | vertically |
| Adds | columns | rows |
| Needs | a matching key | the same number and types of columns |
| Typical use | orders with customer details | web orders with store orders |

The examples are BigQuery SQL, run on 6 October 2026. The logic is the same in any SQL database; the one BigQuery-specific rule is noted where it applies.

**_Note:_** _Originally published in April 2024. Fully updated in October 2026 with one dataset used throughout and every result re-run._

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

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

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

| Join | Keeps | Rows here |
| --- | --- | --- |
| `JOIN` (inner) | matches only | 1 |
| `LEFT JOIN` | every `web` row | 3 |
| `FULL JOIN` | every row from both | 5 |

```
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](/blog/articles/primary-and-foreign-keys-sql) 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](/blog/articles/bigquery-cte-efficient-queries).

## 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](/blog/articles/bigquery-sql-where-vs-having-vs-qualify) before joining, and select only the columns you need.
*   In BigQuery, a join on a [partitioned table](/blog/articles/bigquery-partitioned-tables) 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](https://www.owox.com/app-signup) 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](/product/spreadsheets) or [dashboard](/product/dashboards) 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.

![Data Setup tab of the Unified Ad Spend Data Mart in OWOX Data Marts, defined as SQL on Google BigQuery, validated, with its scan size shown – one owned query reads the tables that several connector Data Marts write](https://cdn.owox.ai/www/content-marketing-article/bigquery-join-and-union-difference/screens/1.png/public)

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

![Models canvas of a demo e-commerce project in OWOX Data Marts with Data Marts such as Products, Pages, Orders, Sessions and Pageviews as cards, and the joins from Pageviews to Pages and Sessions highlighted – each join is declared once and reused](https://cdn.owox.ai/www/content-marketing-article/bigquery-join-and-union-difference/screens/3.png/public)

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

What is the difference between UNION ALL and UNION DISTINCT?

UNION ALL keeps every row from both queries. UNION DISTINCT removes duplicate rows from the combined result, including duplicates that were already inside one of the tables. In BigQuery you must write ALL or DISTINCT; a bare UNION is a syntax error.

Why does a JOIN return a higher total than expected?

Because the key is not unique on the side being joined to. Each row repeats once per match, so a SUM counts it several times. Check with GROUP BY key HAVING COUNT(\*) > 1, and aggregate that table to one row per key before joining.

Can I use JOIN and UNION in the same query?

Yes. A common pattern is to stack the same kind of record from several sources with UNION ALL in a subquery or CTE, adding a column that names the source, and then JOIN the result to a table that describes those records.

What does UNION require of the two queries?

The same number of columns and compatible data types in each position. Columns are matched by position, not by name. In BigQuery, UNION ALL BY NAME matches them by name instead.

How do I choose the correct JOIN type?

By what should happen to rows without a match. An inner JOIN drops them, a LEFT JOIN keeps every row of the first table with NULLs for the missing side, and a FULL JOIN keeps unmatched rows from both. For a report on orders joined to a lookup table, LEFT JOIN is usually right.

What is the difference between JOIN and UNION in SQL?

JOIN combines tables side by side and adds columns, matching rows on a key. UNION combines tables top to bottom and adds rows, and the tables must have the same number of columns with compatible types.

## Who wrote this

![Alyona Samovar](https://cdn.owox.ai/www/webflow/6799099d337aa8a997b03b56_Alyona-Samovar.png/public)

[Alyona Samovar](/team/alyona-samovar) · Senior Digital Analyst

Alyona Samovar is a Senior Digital Analyst who spent years at OWOX helping enterprise clients design analytics systems, build BigQuery reporting pipelines, and optimize marketing measurement. She specializes in SQL-based analytics, data modeling, and turning raw marketing data into actionable insights. Alyona writes about practical analytics workflows, BigQuery best practices, and data-driven decision-making.

![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public)

[Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.

[LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko)

[Google BigQuery](/blog/topics/bigquery)

## Related articles

[Google BigQuery · Comprehensive Guide to BigQuery Aggregate Functions for 2025 · April 18, 2024](/blog/articles/bigquery-aggregate-functions)

[Google BigQuery · BigQuery conversion functions: every type-to-type conversion · October 6, 2026](/blog/articles/bigquery-conversion-functions)

[Google BigQuery · BigQuery DML Commands: A Complete Guide for Data Analysts · March 14, 2024](/blog/articles/bigquery-data-manipulation-language)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google BigQuery](https://www.owox.com/blog/topics/bigquery) — /blog/topics/bigquery.md
- [Alyona Samovar](https://www.owox.com/team/alyona-samovar) — /team/alyona-samovar.md
- [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [primary and foreign keys](https://www.owox.com/blog/articles/primary-and-foreign-keys-sql) — /blog/articles/primary-and-foreign-keys-sql.md
- [common table expression](https://www.owox.com/blog/articles/bigquery-cte-efficient-queries) — /blog/articles/bigquery-cte-efficient-queries.md
- [WHERE clause](https://www.owox.com/blog/articles/bigquery-sql-where-vs-having-vs-qualify) — /blog/articles/bigquery-sql-where-vs-having-vs-qualify.md
- [partitioned table](https://www.owox.com/blog/articles/bigquery-partitioned-tables) — /blog/articles/bigquery-partitioned-tables.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [spreadsheet](https://www.owox.com/product/spreadsheets) — /product/spreadsheets.md
- [dashboard](https://www.owox.com/product/dashboards) — /product/dashboards.md
- [Google BigQuery · Comprehensive Guide to BigQuery Aggregate Functions for 2025 · April 18, 2024](https://www.owox.com/blog/articles/bigquery-aggregate-functions) — /blog/articles/bigquery-aggregate-functions.md
- [Google BigQuery · BigQuery conversion functions: every type-to-type conversion · October 6, 2026](https://www.owox.com/blog/articles/bigquery-conversion-functions) — /blog/articles/bigquery-conversion-functions.md
- [Google BigQuery · BigQuery DML Commands: A Complete Guide for Data Analysts · March 14, 2024](https://www.owox.com/blog/articles/bigquery-data-manipulation-language) — /blog/articles/bigquery-data-manipulation-language.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md