NULLIF()

Syntax

How it works

NULLIF(a, b) is a small but sharp tool from the ANSI SQL standard. The rule is simple: if a equals b it returns NULL, and in every other case (including when either value is NULL) it returns a. It is defined as shorthand for CASE WHEN a = b THEN NULL ELSE a END, which is why it works identically across MySQL, PostgreSQL, SQL Server and SQLite. It sits in the conditional group of the SQL functions library next to CASE and COALESCE.

The headline use is avoiding a divide-by-zero error. Writing x / NULLIF(y, 0) turns the divisor into NULL whenever y is 0, and dividing by NULL yields NULL instead of raising an error or aborting the query. This is the standard, portable pattern for safe division.

The other everyday use is treating a sentinel value as NULL. If a column stores an empty string, a -1, or some other placeholder to mean "no value", NULLIF(col, '') or NULLIF(col, -1) converts that placeholder into a real NULL so the rest of your logic can rely on proper NULL handling. It pairs naturally with COALESCE() to then supply a default.

Examples

Safe division (avoid divide-by-zero)

-- Without NULLIF this errors when total_visits is 0.
-- With it, the ratio is NULL for those rows instead.
SELECT page,
       conversions,
       total_visits,
       conversions / NULLIF(total_visits, 0) AS conversion_rate
FROM traffic;

page    | conversions | total_visits | conversion_rate
--------+-------------+--------------+----------------
 /home  |          50 |          400 |          0.125
 /promo |           0 |            0 |         (NULL)
 /about |           3 |          120 |          0.025

Turn an empty string into NULL

-- middle_name stores '' when it was never filled in.
-- Convert those blanks to real NULLs.
SELECT id,
       middle_name             AS raw_value,
       NULLIF(middle_name, '') AS cleaned
FROM customers;

id | raw_value | cleaned
---+-----------+--------
  1| Lee       | Lee
  2|           | (NULL)
  3| Ann       | Ann

Safe division with a default (NULLIF + COALESCE)

-- Return 0 instead of NULL when the divisor is 0.
SELECT rep,
       COALESCE(revenue / NULLIF(deals, 0), 0) AS avg_deal_value
FROM sales;

rep   | avg_deal_value
------+---------------
 Sara |         1200.0
 Omar |            0.0
 Lina |          850.0

Clean a numeric sentinel value

-- The import used -1 to mean "unknown" for age.
-- Treat -1 as NULL so averages ignore it.
SELECT AVG(NULLIF(age, -1)) AS real_avg_age
FROM users;

real_avg_age
------------
       34.7

Common mistakes

-- NULLIF only takes TWO arguments. This is not a
-- way to say "NULL if the value is any of these".
SELECT NULLIF(status, 'draft', 'archived') FROM posts;

-- NULLIF compares exactly two values. For a list,
-- use CASE (or COALESCE for the opposite job).
SELECT CASE WHEN status IN ('draft', 'archived')
            THEN NULL ELSE status END
FROM posts;

-- Expecting the SECOND argument back. NULLIF never
-- returns value2; it returns value1 or NULL.
SELECT NULLIF(discount, 0) AS wants_zero
FROM orders;  -- returns discount, or NULL when it is 0

-- If you want a fallback value, that is COALESCE's job.
SELECT COALESCE(discount, 0) AS discount_or_zero
FROM orders;

Performance

NULLIF() is expanded internally into a simple CASE expression, so it costs a single comparison per row and adds no meaningful overhead. It is evaluated row by row and does not itself prevent index use.

Be aware, though, that wrapping a column in NULLIF() inside a WHERE clause (for example WHERE NULLIF(col, 0) IS NULL) makes the predicate non-sargable, so the optimiser cannot use an index on that column. Keep the function in the SELECT list, or filter on the raw column, when performance matters.

Interview questions

SELECT amount / NULLIF(quantity, 0) AS unit_price
FROM line_items;

SELECT NULLIF(email, '') AS email
FROM signups;