IFNULL / NULLIF
Two small, easy-to-confuse NULL helpers. IFNULL(a, b) returns b when a is NULL, a two-argument COALESCE. NULLIF(a, b) returns NULL when a = b (otherwise a) — the inverse: it creates a NULL from a sentinel value like 0 or '' so that downstream aggregates ignore it.
Syntax #
sql
-- Replace NULL with a fallback
SELECT IFNULL(col, fallback) FROM table_name;
-- Turn a sentinel into NULL
SELECT NULLIF(col, 0) FROM table_name;Example #
Loading SQL editor...
Tip
Use NULLIF to protect against divide-by-zero. x / NULLIF(y, 0) returns NULL when y = 0 instead of erroring (or silently returning Infinity on floats). Combined with COALESCE you can also display a default: COALESCE(x / NULLIF(y, 0), 0). For the opposite direction, prefer COALESCE over IFNULL — same result for two arguments, but COALESCE is standard SQL and accepts any number of fallbacks.