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

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 NULL

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

All BigQuery lessons