ConditionalSQL standard

NULLIF

Returns NULL if two values are equal, otherwise the first value.

Syntax

NULLIF(<value>, <compare with>)

Compares two arguments: if they're equal, it returns NULL; if not, the first one unchanged. Sounds odd, but it's a handy way to turn a special value into NULL: a zero before division or an empty string before COALESCE.

Equal — NULL, different — the first value

SELECT NULLIF(5, 5) AS same,

NULLIF(5, 3) AS different;

same	different
NULL	5

Division without a division-by-zero error

SELECT 100 / NULLIF(0, 0) AS result;

result
NULL

Without NULLIF, the query SELECT 100 / 0 fails with division by zero. Dividing by NULL simply gives NULL.

Together with COALESCE: an empty string becomes a dash

SELECT COALESCE(NULLIF('', ''), '—') AS empty_phone,

COALESCE(NULLIF('+44 7700 900123', ''), '—') AS filled_phone;

empty_phone	filled_phone
—	+44 7700 900123

Watch out

The name is misleading: NULLIF doesn't replace NULL — quite the opposite, it creates one. Replacing NULL with a value is what COALESCE does.

The second argument must have a type compatible with the first: NULLIF(5, 'a') throws invalid input syntax for type integer.

Related functions

COALESCE CASE

Learn more in the course

Handling NULL: COALESCE and NULLIF