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".
SELECT 100 / NULLIF(0, 0); -- NULL, no error
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 isNULLthe comparison is not true andexpr1is returned.NULLIF(NULL, 5)isNULLsimply because the first argument isNULL. - Safe division.
a / NULLIF(b, 0)returnsNULLwhenbis zero. Combine withCOALESCE(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 toNULL, so laterIS NULLchecks andCOALESCEwork as expected. - Aggregates.
AVG(NULLIF(score, 0))ignores zeros because aggregates skipNULL. - Integer division.
NULLIFdoes not change the division type. In SQLite and most databases7 / 2on integers is 3; multiply by1.0if you need decimals. - Pairs with
COALESCE. Together they turn one placeholder into another in two steps.