How to Use NULLIF in BigQuery
Last updated August 23, 2026 · By the SaturnSQL team
NULLIF(a, b) returns NULL when the two values are equal, otherwise a. The usual use is NULLIF(denom, 0), so a division returns NULL instead of erroring.
SELECT revenue / NULLIF(orders, 0) AS avg_order_value
FROM daily_totals;Why the division needs it
Dividing by zero raises an error in BigQuery and fails the whole query, not just the row. NULLIF turns the zero into a NULL, and dividing by NULL gives NULL. SAFE_DIVIDE does the same thing in one function and is the more direct choice when zero is the only case you are guarding against.
SELECT SAFE_DIVIDE(revenue, orders) AS avg_order_value
FROM daily_totals;Normalising placeholder values
The other common use is turning sentinel values into real NULLs on the way out of a legacy table, so that empty strings or -1 stop being counted as data. Chain it with COALESCE when you want a different fallback afterwards.
SELECT COALESCE(NULLIF(TRIM(country), ''), 'unknown') AS country
FROM users;Comparison, not coalescing
NULLIF is easy to confuse with IFNULL, which does the opposite: IFNULL(a, b) replaces a NULL with b, while NULLIF creates a NULL. NULLIF also requires the two arguments to be comparable types, so mixing an INT64 and a STRING is an error rather than a silent cast.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free