LEFT JOIN and anti-joins
Some of the best questions are about what is missing, such as vehicles with no test this year. A LEFT JOIN plus an IS NULL check finds them.
How it works
A LEFT JOIN keeps every row of the left table. Where the right table has no match, its columns come back as NULL. Filtering on that NULL keeps only the left rows that found no match, which is called an anti-join.
- Write
FROM a LEFT JOIN b ON a.key = b.key WHERE b.key IS NULLto keep the rows of a that have no match in b. - A condition that decides which right-side rows count, such as a date range on the right table, belongs in
ON. InWHEREit removes theNULLrows and theLEFT JOINbehaves like anINNER JOIN. - A condition on the left table belongs in
WHERE. - A table that requires a partition filter, such as
fact_mot_test, can be on either side of aLEFT JOIN. On the left, write its filter inWHERE, because BigQuery rejects the query when the filter is only inON. On the right, the filter can stay inON. Either placement reads the same bytes, so the rule is about what BigQuery allows rather than about cost.
Common mistake
A range filter on the right table written in WHERE throws away the unmatched rows that the LEFT JOIN kept. Combined with IS NULL it can never be true, so the query returns nothing and shows no error.
Worked example
SELECT d.registration, d.make
FROM `learn.mot.dim_vehicle` AS d
LEFT JOIN `learn.mot.fact_vehicle_summary` AS s ON d.registration = s.vehicle_id
WHERE s.vehicle_id IS NULL
ORDER BY d.registration
LIMIT 5Every dim_vehicle row stays. Where no summary row matches, the s columns are NULL.
The WHERE keeps only those rows, so the result lists vehicles that have no summary.