Querying

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.

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

123456789101112131415

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

	customer_id	name	order_id	amount
1	1	ana	o1	30
2	1	ana	o2	45

2 rows

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

123456789101112131415

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

	customer_id	name	order_id
1	1	ana	o1
2	1	ana	o2
3	2	ben	null
4	3	null	o3
5	null	guest	null
6	null	null	o4

6 rows

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

123456789101112131415

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

	name	order_id
1	ana	o1
2	ana	o2
3	ben	null
4	guest	null

4 rows

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

the guest matches the order with no customer ID

123456789101112131415

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

	name	order_id
1	ana	o1
2	ana	o2
3	guest	o4

3 rows

See NULL in BigQuery.

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

123456789101112

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

	credit_after_join	order_total
1	200	75

1 row

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 flags “Join Explosion Detected” when a join stage writes many times more rows than it read. The same happens after UNNEST.

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

12345678910111213

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

	row_count
1	12

1 row

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

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

123456789

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

	event_id	session_id
1	e1	s1

1 row

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

the same as a LEFT JOIN

123456789

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

	event_id	session_id
1	e1	s1

1 row

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 columnBigQuery rejects this query

123

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

Error in BigQuery

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.

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

123456789101112131415

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

	name
1	ana

1 row

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.