NULL and IS NULL
Real data has gaps, such as a missing date or an unrecorded colour. If you do not handle NULL on purpose, rows disappear from your results with no error.
How it works
NULL means the value is unknown or absent. Any comparison with NULL, including equality, gives NULL instead of true. WHERE keeps only the rows where the condition is true, so a NULL never matches = or <>.
- Test for a missing value with
IS NULLandIS NOT NULL. BigQuery refuses= NULLand<> NULLwith an error. COALESCE(a, b, ...)returns the first value that is notNULL. The values must share a type, so turn a number into text withCAST(number AS STRING)before you mix it with text. The Dates and strings lesson coversCASTin full.IFNULL(a, b)is the two-argument form ofCOALESCE.dim_vehicleis a history table: it holds one row for every version of a vehicle's details. Filter on the top-levelis_current = TRUEto get one row per vehicle. The teaching sample holds current rows only, but write the filter anyway so the query stays right on the real table, which keeps its older rows.dim_vehicleandfact_vehicle_summaryare not partitioned, so they need no date filter.
Common mistake
A filter such as make <> 'FORD' drops the rows where make is NULL, because the comparison is never true for them. Add OR make IS NULL when you want to keep them.
Worked example
SELECT registration, spec.model
FROM `learn.mot.dim_vehicle`
WHERE make = 'HONDA'
AND is_current = TRUE
AND dates.last_mot_test_date IS NULLIS NULL keeps the Honda vehicles that have no last_mot_test_date on record.
is_current = TRUE keeps one row per vehicle. The table has no partition filter, so the query needs no date range.
In the Schema tab, a column that is allowed to hold NULL is marked NULLABLE.
The teaching sample holds current rows only, so each vehicle has one row here.