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.
x = NULLis not a test forNULL, and BigQuery refuses it withOperands of = cannot be literal NULL. Writex IS NULL.a.k IS NOT DISTINCT FROM b.kis true when both sides are equal or both areNULL, and false otherwise. It is neverNULL.IS DISTINCT FROMis its opposite. Write it in theONclause, where it decides which rows pair up. InWHEREit filters rows after they are paired.- BigQuery joins quickly by hashing the key of an equality. It cannot hash
IS NOT DISTINCT FROM, so as the only condition it compares every row of one side with every row of the other. - Beside a plain equality, as in
a.j = b.j AND a.k IS NOT DISTINCT FROM b.k, the hash coversjonly, and the test onkruns on the rows that share aj. That helps as far asjnarrows the match. a.k = b.k OR (a.k IS NULL AND b.k IS NULL)joins the same rows, and BigQuery hasheskitself with it.- Measured on the real
dim_vehiclejoined to itself (5,500 rows on each side):IS NOT DISTINCT FROMalone took 598 slot-ms and planned a cross join, withfuel_type = fuel_typebeside it 276, and theORform 12. All of them read the same bytes, so the difference is in slot time. How large it is depends on the data. - A key made of several columns can mix both. Columns that are never
NULLkeep=, and only the column that can beNULLneeds the null-safe form. - Swapping
NULLfor a made-up value on both sides, as inCOALESCE(a.k, -1) = COALESCE(b.k, -1), joins the same rows only while no real row holds that value.
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 = TRUEThis 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.