conditional
NULLIF
Returns NULL if value1 equals value2, otherwise returns value1. Handy for converting sentinel values (empty strings, 0) into NULL, or safely guarding against division by zero.
NULLIF(value1, value2)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| NULLIF(x, '') | Common pattern to turn empty strings into NULLs |
| NULLIF(denom, 0) | Divide-by-zero guard: numerator / NULLIF(denom, 0) |
Examples
SELECT NULLIF(username, '') FROM users;Turn empty strings into NULLs
SELECT amount / NULLIF(qty, 0) FROM sales;Divide-by-zero becomes NULL instead of error
SELECT NULLIF(status, 'unknown') FROM events;Erase 'unknown' markers so COUNT() ignores them
SELECT NULLIF(NULL, 'x');Returns NULL — NULL is never equal to anything, but NULLIF propagates the first arg
Dialect notes / Gotcha
NULLIF uses SQL equality — NULL vs anything is UNKNOWN, so NULLIF(NULL, x) returns NULL (the first argument). Types must be comparable; mixing text and int raises an error.
Related functions
COALESCE
Returns the first non-NULL argument, or NULL if every argument is NULL. Short-circuit evaluated — later expressions are not computed once a non-NULL value is found.
CASE WHEN
SQL's portable if/else-if expression. Evaluates each WHEN clause in order and returns the first matching result; returns NULL from an unmatched CASE without ELSE.
GREATEST / LEAST
Return the largest (GREATEST) or smallest (LEAST) of the provided arguments row-by-row — not across rows, which is MIN/MAX. NULL handling differs by engine: Postgres ignores NULL args, MySQL returns NULL if any argument is NULL.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet