# Joins in BigQuery

> How JOIN behaves in BigQuery. USING merges the key column, NULL keys never match unless you use IS NOT DISTINCT FROM, a comma is a CROSS JOIN, range conditions work in outer joins, arrays can't be join keys, and duplicate keys multiply other columns' sums.

Source: https://docs.querylab.io/bigquery/joins/

Updated: 2026-10-14

Joins in BigQuery use the standard `INNER`, `LEFT`, `RIGHT`, `FULL` and `CROSS` forms. A few details differ from other databases or catch people out: how `USING` shapes the output, what happens to NULL keys, and how a join can quietly multiply a sum. For speed and cost, see [Optimizing joins](https://docs.querylab.io/bigquery/optimizing-joins/). The examples use three customers (one without an ID) and four orders (one for an unknown customer, one without a customer ID).

## USING vs ON

`JOIN ... USING (customer_id)` joins on columns with the same name and returns that column once. With `SELECT *` you get one `customer_id`, not two:

**SELECT * with USING**

```sql
WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT NULL, 'guest'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 3, 20 UNION ALL
  SELECT 'o4', NULL, 10
)
SELECT *
FROM customers
JOIN orders USING (customer_id)
ORDER BY order_id
```

Result in BigQuery (2 rows):

| customer_id | name | order_id | amount |
| --- | --- | --- | --- |
| 1 | ana | o1 | 30 |
| 1 | ana | o2 | 45 |

With `ON a.customer_id = b.customer_id`, both columns are kept and must be qualified when you select them.

In a `FULL JOIN ... USING`, the merged column takes its value from whichever side has one, like `COALESCE(a.customer_id, b.customer_id)`. That is usually what you want:

**FULL JOIN with USING fills the key from both sides**

```sql
WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT NULL, 'guest'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 3, 20 UNION ALL
  SELECT 'o4', NULL, 10
)
SELECT customer_id, c.name, o.order_id
FROM customers AS c
FULL JOIN orders AS o USING (customer_id)
ORDER BY customer_id NULLS LAST, o.order_id
```

Result in BigQuery (6 rows):

| customer_id | name | order_id |
| --- | --- | --- |
| 1 | ana | o1 |
| 1 | ana | o2 |
| 2 | ben | NULL |
| 3 | NULL | o3 |
| NULL | guest | NULL |
| NULL | NULL | o4 |

## NULL keys never match

`NULL = NULL` isn't true, so rows with a NULL key don't join to anything. In a `LEFT JOIN`, the guest customer with no ID stays, with no orders:

**customers and their orders**

```sql
WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT NULL, 'guest'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 3, 20 UNION ALL
  SELECT 'o4', NULL, 10
)
SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id
```

Result in BigQuery (4 rows):

| name | order_id |
| --- | --- |
| ana | o1 |
| ana | o2 |
| ben | NULL |
| guest | NULL |

To make NULL keys match each other, join with `IS NOT DISTINCT FROM`:

**the guest matches the order with no customer ID**

```sql
WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT NULL, 'guest'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 3, 20 UNION ALL
  SELECT 'o4', NULL, 10
)
SELECT c.name, o.order_id
FROM customers AS c
JOIN orders AS o ON o.customer_id IS NOT DISTINCT FROM c.customer_id
ORDER BY c.name, o.order_id
```

Result in BigQuery (3 rows):

| name | order_id |
| --- | --- |
| ana | o1 |
| ana | o2 |
| guest | o4 |

See [NULL in BigQuery](https://docs.querylab.io/bigquery/null-handling/).

## A join multiplies the other side

When a key appears twice on one side, every matching row on the other side is repeated twice. Summing a column from the side with a single row then double counts it. Ana has a credit limit of 100 and two orders:

**the credit limit is summed once per order**

```sql
WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name, 100 AS credit_limit
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45
)
SELECT
  SUM(c.credit_limit) AS credit_after_join,
  SUM(o.amount) AS order_total
FROM customers AS c
JOIN orders AS o USING (customer_id)
```

Result in BigQuery (1 row):

| credit_after_join | order_total |
| --- | --- |
| 200 | 75 |

The order total of 75 is right; the credit limit of 200 isn't. Aggregate each side to the join key before joining, or sum only columns from the side that has one row per result row. After a run, Querylab.io's [execution plan](https://docs.querylab.io/writing-sql/execution-plan/) flags "Join Explosion Detected" when a join stage writes many times more rows than it read. The same happens after [UNNEST](https://docs.querylab.io/bigquery/unnest/#unnesting-multiplies-the-other-columns).

## Comma joins

A comma between two tables in `FROM` is a `CROSS JOIN`: every row with every row. Three customers and four orders give twelve rows:

**FROM customers, orders**

```sql
WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT NULL, 'guest'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 3, 20 UNION ALL
  SELECT 'o4', NULL, 10
)
SELECT COUNT(*) AS row_count
FROM customers, orders
```

Result in BigQuery (1 row):

| row_count |
| --- |
| 12 |

A comma followed by `UNNEST` of a column, as in `FROM orders, UNNEST(orders.items)`, is also a cross join, but a correlated one: each row only pairs with its own array's elements. That is the normal way to flatten arrays. Querylab.io flags a comma or explicit cross join with an Info-level "Cross Join Detected" rule and offers to rewrite it as `CROSS JOIN` or move the condition into an `ON` clause (see [code quality](https://docs.querylab.io/writing-sql/code-quality/)).

## Conditions other than equality

Joins on ranges work, for example matching events to the session they fall into:

**an event inside a session's time range**

```sql
WITH events AS (
  SELECT 'e1' AS event_id, TIMESTAMP '2026-03-01 10:05:00' AS at_time
),
sessions AS (
  SELECT 's1' AS session_id, TIMESTAMP '2026-03-01 10:00:00' AS started, TIMESTAMP '2026-03-01 10:30:00' AS ended
)
SELECT e.event_id, s.session_id
FROM events AS e
JOIN sessions AS s ON e.at_time BETWEEN s.started AND s.ended
```

Result in BigQuery (1 row):

| event_id | session_id |
| --- | --- |
| e1 | s1 |

Older answers online say BigQuery rejects a `LEFT JOIN` whose condition has no equality. It runs today:

**the same as a LEFT JOIN**

```sql
WITH events AS (
  SELECT 'e1' AS event_id, TIMESTAMP '2026-03-01 10:05:00' AS at_time
),
sessions AS (
  SELECT 's1' AS session_id, TIMESTAMP '2026-03-01 10:00:00' AS started, TIMESTAMP '2026-03-01 10:30:00' AS ended
)
SELECT e.event_id, s.session_id
FROM events AS e
LEFT JOIN sessions AS s ON e.at_time BETWEEN s.started AND s.ended
```

Result in BigQuery (1 row):

| event_id | session_id |
| --- | --- |
| e1 | s1 |

Without an equality in the condition, rows can't be matched by key, and BigQuery ends up comparing far more pairs of rows, which gets slow on large tables. When you can, add an equality on a bucket (like the date) next to the range condition.

## Arrays can't be join keys

Equality isn't defined for arrays, so they can't be compared in `ON`:

**joining on an array column**

```sql
WITH a AS (SELECT [1, 2] AS k), b AS (SELECT [1, 2] AS k)
SELECT COUNT(*) AS matches
FROM a JOIN b ON a.k = b.k
```

BigQuery rejects this query:

```text
Equality is not defined for arguments of type ARRAY<INT64> at [3:18]
```

Join on a scalar, or on `TO_JSON_STRING(array)` if the arrays must match exactly. Structs can be join keys; they compare field by field. See [STRUCT](https://docs.querylab.io/bigquery/structs/#comparing-structs).

## Semi-joins: EXISTS and IN

To keep rows that have a match without bringing in the other side's columns, or multiplying rows, use `EXISTS` or `IN` instead of a join:

**customers who have at least one order**

```sql
WITH customers AS (
  SELECT 1 AS customer_id, 'ana' AS name UNION ALL
  SELECT 2, 'ben' UNION ALL
  SELECT NULL, 'guest'
),
orders AS (
  SELECT 'o1' AS order_id, 1 AS customer_id, 30 AS amount UNION ALL
  SELECT 'o2', 1, 45 UNION ALL
  SELECT 'o3', 3, 20 UNION ALL
  SELECT 'o4', NULL, 10
)
SELECT name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id)
ORDER BY name
```

Result in BigQuery (1 row):

| name |
| --- |
| ana |

For the opposite, rows without a match, prefer `NOT EXISTS` over `NOT IN`, which returns nothing if the subquery contains a NULL. See [NOT IN with a NULL](https://docs.querylab.io/bigquery/null-handling/#not-in-with-a-null-returns-nothing).

Join two tables on a key that has NULLs in it. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Query syntax, JOIN operation](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#join_types)

- [Optimize query computation, joins](https://cloud.google.com/bigquery/docs/best-practices-performance-compute)