NULLIF Function
SQL · Conditional Expressions
Theory
NULLIF(a, b) compares two values: if they are equal it returns NULL, otherwise it returns a. NULLIF(qty, 0) gives NULL when qty is 0 and qty otherwise. It is the same as CASE WHEN a = b THEN NULL ELSE a END.
Its best-known job is preventing divide-by-zero errors. total / qty fails (or returns NULL or an error, depending on the engine) when qty is 0, but total / NULLIF(qty, 0) returns NULL for those rows and the query keeps running. It also cleans placeholder values: NULLIF(phone, '') turns empty strings into real NULLs so COUNT and COALESCE treat them as missing. A common pattern combines both functions: COALESCE(total / NULLIF(qty, 0), 0) shows 0 instead of NULL. Watch out for integer division in some engines, where 7 / 2 is 3.
Why we use it
Zero divisors and fake "empty" values (0, '', 'N/A') break calculations and counts. NULLIF turns them into NULL, which SQL already knows how to skip and handle safely.
When to use it
Use NULLIF to guard divisions and to convert placeholder values into NULL. Use COALESCE for the opposite direction, turning NULL into a value, and CASE for anything more complex than one equality test.
How it works
- NULLIF(a, b) for a row
- Compare a with b
- Are they equal? Yes: Return NULL. No: Return a.
- Use the result in the outer expression, e.g. total / result
- Division by NULL gives NULL instead of an error
Syntax
NULLIF(value1, value2)Example
SELECT NULLIF(discount, 0) FROM orders;Key points
- Useful to avoid divide-by-zero errors
- Opposite intent of COALESCE: value to NULL instead of NULL to value
- Same as CASE WHEN a = b THEN NULL ELSE a END
- NULLIF(col, '') turns empty strings into NULLs
- Wrap in COALESCE to show 0 instead of NULL