Joining on keys that can be NULL

A join on a column that can be NULL drops those rows without an error. Totals and averages built on the join then come out wrong, and the query still looks fine.

How it works

A join pairs two rows when its ON condition is true. NULL = NULL is NULL, which is not true, so a row whose key is NULL finds no partner. An INNER JOIN drops it, and a LEFT JOIN keeps it with NULL columns from the other side. Other parts of SQL treat all the NULLs of a column as one value: GROUP BY puts them in one group, and DISTINCT keeps one of them.

Common mistake

A join gives no error when it drops the rows with a NULL key. Compare the row count before and after the join when a key can be NULL.

Worked example

SELECT COUNT(*) AS tests
FROM `learn.mot.fact_mot_test` AS f
JOIN `learn.mot.dim_vehicle` AS d
  ON f.vehicle_id = d.registration
  AND f.vehicle_snapshot.engine_size IS NOT DISTINCT FROM d.spec.engine_size
WHERE f.completed_date BETWEEN '2024-03-01' AND '2024-03-31'
  AND d.is_current = TRUE

This counts the March 2024 tests whose engine size at the test equals the engine size on the vehicle record. The key is the vehicle plus its engine size.

The vehicle part is a plain equality, so BigQuery can still hash the join. Only the engine size, which can be NULL, uses the null-safe form. With = on it, the tests of vehicles with no engine size would drop out of the count.

All BigQuery lessons