NULLIF

NULLIF is a small but genuinely useful function: it compares two expressions, and if they are equal it returns NULL; otherwise it returns the first expression unchanged. It is, in a sense, the mirror image of COALESCE — where COALESCE turns NULL into a real value, NULLIF turns a specific real value into NULL.

NULLIF syntax

SQL

NULLIF(expr1, expr2)
-- returns NULL if expr1 = expr2, otherwise returns expr1

Basic behavior

SQL

SELECT NULLIF(5, 5);   -- NULL, because the two arguments are equal
SELECT NULLIF(5, 10);  -- 5, because they are not equal
SELECT NULLIF('', ''); -- NULL

Avoiding division by zero

The single most common reason people reach for NULLIF is to guard against a division-by-zero error. Dividing by zero normally raises an error and can halt your entire query. Wrapping the denominator in NULLIF(denominator, 0) converts a zero denominator into NULL before the division ever happens — and dividing by NULL simply produces NULL, not an error:

Division by zero, unprotected

SQL

-- If total_attempts is ever 0 for a row, this raises a
-- "division by zero" error and can abort the whole query.
SELECT
  product_id,
  successful_orders / total_attempts AS conversion_rate
FROM product_stats;

Fixed with NULLIF

SQL

SELECT
  product_id,
  successful_orders / NULLIF(total_attempts, 0) AS conversion_rate
FROM product_stats;

product_id | conversion_rate
-----------+-----------------
1          | 0.42
2          | NULL
3          | 0.18

Product 2 had zero total_attempts. Instead of the query erroring out, that row cleanly reports NULL — “conversion rate is not defined here” — which is both accurate and safe to handle downstream.

NULLIF and COALESCE together

NULLIF and COALESCE are frequently paired: NULLIF converts an unwanted value into NULL, and COALESCE then supplies a fallback for that NULL. For example, treating an empty string the same as a missing value, and falling back to a default:

Treating '' the same as NULL, with a fallback

SQL

SELECT
  id,
  COALESCE(NULLIF(middle_name, ''), 'N/A') AS middle_name_display
FROM users;

Here, NULLIF(middle_name, '') turns an empty string into NULL, and the surrounding COALESCE then replaces that NULL (whether it came from an empty string or was already NULL) with 'N/A'.

NULLIF(a, b) returns NULL when a and b are equal, otherwise returns a
Its most common use is guarding a division: numerator / NULLIF(denominator, 0)
Combine it with COALESCE to normalize a "sentinel" value (like an empty string or a placeholder number) into a real default

NULLIF is ANSI standard

Like COALESCE, NULLIF is part of the ANSI SQL standard and behaves consistently across PostgreSQL, MySQL, SQL Server, and Oracle, so it’s safe to rely on regardless of which database you’re targeting.