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.

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 5

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

All BigQuery lessons