skip to content

What does NULLIF(x, 0) return, and why is it the portable division-by-zero guard?

level: middleimportance: nice to knowfreq 33%

answer

  1. It returns NULL when two things match
  2. Standard shorthand for a small CASE
  3. Only ever returns its first argument
  4. Makes a zero denominator disappear
  5. Pair it with the first-non-NULL function

basics

~10 s

NULLIF(x, 0) returns NULL when x equals 0 and x otherwise. Dividing by it turns a would-be division-by-zero error into a NULL result, since any arithmetic with a NULL operand yields NULL.

solid answer

~40 s

`NULLIF(v1, v2)` is standard shorthand for `CASE WHEN v1 = v2 THEN NULL ELSE v1 END`: it returns NULL when the two arguments are equal and the first argument otherwise. Its most common use is turning a zero denominator into a NULL one — `revenue / NULLIF(order_count, 0)` — because NULL propagates through arithmetic, so the expression evaluates to NULL instead of raising a division-by-zero error and aborting the whole statement. That is portable: it relies only on NULLIF and on NULL propagation, not on any engine's error handling. The trade is that the result is NULL rather than a number, so if the report needs a value you wrap it again: `COALESCE(revenue / NULLIF(order_count, 0), 0)`. NULLIF is also useful for normalising a placeholder — `NULLIF(comment, '')` converts empty strings into genuine NULLs.

code

sql · 7 lines
sql
-- Aborts the whole statement on any customer with no orders
SELECT customer_id, revenue / order_count AS revenue_per_order
FROM customer_totals;

-- Zero denominators become NULL; every other row still computes
SELECT customer_id, revenue / NULLIF(order_count, 0) AS revenue_per_order
FROM customer_totals;

go deeper

for a junior

Remember the one-liner: NULLIF returns NULL when its two arguments are equal, otherwise the first one, and dividing by NULLIF(x, 0) avoids a division-by-zero error.

for a middle

Explain it as standard shorthand for CASE WHEN v1 = v2 THEN NULL ELSE v1 END, and explain why the guard works — the NULL denominator propagates through the arithmetic instead of raising an error.

for a senior

Show judgment about the output: a NULL ratio means undefined, and COALESCE-ing it to 0 is a presentation choice that can mislead a reader who averages the column afterwards.

for a principal

Frame the wider policy — whether sentinels and placeholders should be normalised to NULL at ingest rather than patched with NULLIF in every downstream query, and what contract reports get for undefined measures.

## What NULLIF does `NULLIF(v1, v2)` takes exactly two arguments and is defined in the standard as shorthand for a `CASE` expression: ```sql CASE WHEN v1 = v2 THEN NULL ELSE v1 END ``` So it returns NULL when the arguments are equal, and otherwise returns `v1` — never `v2`. Some quick evaluations: ```sql SELECT NULLIF(0, 0); -- NULL SELECT NULLIF(5, 0); -- 5 SELECT NULLIF('', ''); -- NULL SELECT NULLIF('a', 'b'); -- 'a' SELECT NULLIF(NULL, 0); -- NULL ``` The last case follows from the `CASE` definition: `NULL = 0` is UNKNOWN, so the `WHEN` branch is not taken, the `ELSE` returns `v1`, and `v1` is NULL. The result is NULL either way, which is convenient — NULLIF never *introduces* a value where one was missing. The two arguments must be comparable types, since the expression has to evaluate `v1 = v2`, and the declared result type comes from `v1`. ## The division guard The canonical use is protecting a division: ```sql SELECT customer_id, revenue / NULLIF(order_count, 0) AS revenue_per_order FROM customer_totals; ``` Dividing by zero is an error in SQL, and an error aborts the statement — one bad row fails the entire report. `NULLIF(order_count, 0)` converts the offending denominator into NULL, and because arithmetic propagates NULL, the whole quotient becomes NULL for that row. Every other row computes normally. This is worth contrasting with the alternatives. A `CASE` guard does the same job more verbosely and repeats the denominator expression: ```sql CASE WHEN order_count = 0 THEN NULL ELSE revenue / order_count END ``` Some engines offer their own error-suppressing constructs, but those are dialect-specific; `NULLIF` plus NULL propagation is defined by the standard and behaves the same everywhere, which is exactly why it is the idiom people reach for in portable code. ## NULL or zero in the output? A NULL quotient is semantically right — with no orders, revenue per order is genuinely undefined, not zero. But a report may need a printable number, and then you layer the two functions: ```sql COALESCE(revenue / NULLIF(order_count, 0), 0) AS revenue_per_order ``` Read it inside out: `NULLIF` makes the denominator NULL, the division propagates NULL, and `COALESCE` substitutes 0 at the end. Do this deliberately, though — displaying 0 for "undefined" invites a reader to average it in with real zeros and reach a wrong conclusion. Showing a blank, or a separate "n/a" bucket, is usually more honest. ## Other uses **Normalising placeholders.** Legacy data often uses an empty string or a sentinel where a NULL belongs. `NULLIF` converts them on the way out: ```sql SELECT NULLIF(comment, '') AS comment, NULLIF(temperature, -999) AS temperature FROM readings; ``` After that, ordinary `IS NULL` handling works uniformly instead of every consumer needing to know the sentinel. **Suppressing a value you do not want to display.** `NULLIF(status_code, 200)` blanks out the uninteresting common case so only exceptions stand out in a listing. ## Gotchas - **Argument order matters.** `NULLIF(a, b)` and `NULLIF(b, a)` differ whenever the values are unequal: the first returns `a`, the second returns `b`. The value you want to keep goes first. - **It is equality-based.** `NULLIF` compares with `=`, so it cannot blank out a range or a pattern; that needs a `CASE`. - **It only takes two arguments.** `NULLIF` is not a variadic sibling of `COALESCE`; blanking several values needs nesting or a `CASE`. - **Sargability.** Wrapping a filtered column in `NULLIF` makes the predicate non-sargable, so keep it in the select list and in computed expressions rather than around an indexed column in `WHERE`. ## Interview framing Define it via its `CASE` equivalent, show the division guard, then add the honest note that the guard produces NULL rather than a number and that turning that NULL into 0 is a presentation decision, not a correctness one.

  • What does NULLIF return when its first argument is NULL?
    NULL. From the `CASE` definition, `NULL = v2` evaluates to UNKNOWN, so the `WHEN` branch is not taken and the expression falls through to `ELSE v1`, which is NULL. Useful property: NULLIF never turns an absent value into a present one, so it is safe to apply over columns that may already be NULL.
  • How do you show 0 instead of NULL for a guarded division?
    Wrap the whole quotient: `COALESCE(revenue / NULLIF(order_count, 0), 0)`. NULLIF makes the denominator NULL, the division propagates it, and COALESCE substitutes the default at the end. Consider whether that is honest, though — displaying 0 for an undefined ratio lets a reader average it in with genuine zeros.
  • Does NULLIF(a, b) ever return b?
    No. It returns NULL when the two are equal and `a` otherwise, so `b` is only ever used as the comparison operand. That makes argument order significant: `NULLIF(b, a)` is a different expression, returning `b` when the values differ.

saying these in an interview costs you the question

  • Thinking NULLIF can return its second argument
  • Believing NULLIF suppresses the division error itself rather than the zero
  • Treating NULLIF as variadic like COALESCE
  • Assuming NULLIF(NULL, 0) returns 0
  • Wrapping an indexed column in NULLIF inside a WHERE predicate

context

resolving links…