Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL NULLIF() function

Reference for SQL NULLIF(): return NULL when two values are equal, avoid division by zero, turn placeholder values into NULL, and how it relates to CASE.

NULLIF(a, b) returns NULL if a equals b, and returns a otherwise. It is the opposite of COALESCE: where COALESCE replaces NULL with a value, NULLIF turns a particular value into NULL.

Two uses account for almost all of its appearances: avoiding division by zero (dividing by NULL gives NULL, not an error) and cleaning data in which an empty string or -1 stands for "unknown".

SQL
SELECT 100 / NULLIF(0, 0);   -- NULL, no error

Try it

SQL Division by zero becomes NULL ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Placeholders become NULL ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL NULLIF compared with CASE, and with NULL arguments ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
NULLIF(expr1, expr2)
Part Description
expr1 The value to return when the two differ.
expr2 The value to compare against.

Returns: NULL if expr1 = expr2 is true; otherwise expr1. It is equivalent to CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

Dialect differences

Database Notes
SQLite Standard behaviour.
PostgreSQL Standard; both arguments must be of comparable types.
MySQL Standard. Text comparison follows the column's collation.
SQL Server Standard. Note that expr1 may be evaluated twice, which matters for non-deterministic expressions such as RAND().
Oracle Standard, but the first argument cannot be the literal NULL. Since an empty string is NULL in Oracle, NULLIF(x, '') is not needed there.

Usage notes

  • The comparison uses =, so if either argument is NULL the comparison is not true and expr1 is returned. NULLIF(NULL, 5) is NULL simply because the first argument is NULL.
  • Safe division. a / NULLIF(b, 0) returns NULL when b is zero. Combine with COALESCE(a / NULLIF(b, 0), 0) if you want 0 instead. Be sure that 0 is a meaningful answer for your report.
  • Empty strings. NULLIF(TRIM(phone), '') converts blank values to NULL, so later IS NULL checks and COALESCE work as expected.
  • Aggregates. AVG(NULLIF(score, 0)) ignores zeros because aggregates skip NULL.
  • Integer division. NULLIF does not change the division type. In SQLite and most databases 7 / 2 on integers is 3; multiply by 1.0 if you need decimals.
  • Pairs with COALESCE. Together they turn one placeholder into another in two steps.