NULLIF

Definition

NULLIF(val1, val2) returns NULL if val1 equals val2, otherwise returns val1 unchanged — essentially the inverse operation of COALESCE: instead of replacing a NULL with a fallback, it deliberately CREATES a NULL when a specific "sentinel" value is seen.

How It Works

sql
SELECT name, NULLIF(stock, 0) AS stock_or_null FROM products;

For ProPhone (stock = 0), this returns NULL instead of 0 — useful when 0 is being used as a placeholder for "not really tracked" and you want later calculations (like AVG, which ignores NULLs but not zeros) to skip those rows rather than treating them as a real zero value.

A very common real use: safely avoiding division by zero. total_revenue / NULLIF(total_orders, 0) returns NULL (not an error, and not an incorrect result) when total_orders is 0, instead of the division itself failing or producing an undefined result.

Edge Cases and Pitfalls

• NULLIF(a, b) is exactly equivalent to CASE WHEN a = b THEN NULL ELSE a END — it's really just a concise shorthand for a very specific, common CASE pattern.
• Combining NULLIF and COALESCE together is a common, powerful pattern: COALESCE(NULLIF(discount_code, ''), 'NONE') treats an empty string the same as a missing value, converting it to NULL first (via NULLIF) so COALESCE can then supply a real fallback.
• NULLIF's equality check follows ordinary comparison semantics — if val1 is already NULL, NULLIF(NULL, anything) returns NULL (since NULL = anything is UNKNOWN, not TRUE, so the "otherwise return val1" branch applies, and val1 IS NULL).

Key Takeaways

• NULLIF(a, b) returns NULL if a equals b, otherwise returns a — the inverse of COALESCE's "replace NULL" behavior.
• Its most common real use is safe division: value / NULLIF(divisor, 0) avoids a division-by-zero error.
• NULLIF(a, b) is shorthand for CASE WHEN a = b THEN NULL ELSE a END.