Return NULL if two expressions are equal, otherwise return the first.
NULLIF(expr1, expr2)## Overview Compares expr1 and expr2 using standard SQL equality. If they are equal, NULLIF returns NULL; otherwise it returns expr1. The common pattern is to convert sentinel values (for example 'N/A', 'UNKNOWN', empty string, or zero) into SQL NULL so downstream NULL-handling tools (COALESCE, IS NULL) can work as intended. The function is equivalent to CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END. ## Behavior - Returns NULL when expr1 = expr2 is TRUE. - Returns expr1 when expr1 = expr2 is FALSE or UNKNOWN. - When expr1 is NULL, the equality is UNKNOWN, so NULLIF returns NULL (which is expr1). - Result type is the common supertype of the two arguments. - Does not treat distinct-but-equivalent values as equal unless the engine's = operator does. ## Compatibility - NULLIF is standard SQL (SQL:1992) and is supported by every major SQL engine with identical semantics. - Equivalent to CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
| Name | Type | Description |
|---|---|---|
expr1 | Specifies the expression to return when the two arguments are not equal. Also the left operand of the equality comparison. | |
expr2 | Specifies the expression to compare against expr1. Must be coercible to a common supertype with expr1. |
-- Different values: returns the first
SELECT NULLIF(10, 20) AS v; -- 10-- Equal values: returns NULL
SELECT NULLIF(10, 10) AS v; -- NULL-- Avoid division by zero
SELECT 100.0 / NULLIF(divisor, 0) AS ratio
FROM (VALUES (0), (5)) AS t(divisor);-- String comparison
SELECT NULLIF('N/A', 'N/A') AS v; -- NULL-- Realistic: convert sentinel values to NULL on read
SELECT user_id, NULLIF(status, 'UNKNOWN') AS status
FROM iam.directory.users;-- Combine with COALESCE to map multiple sentinels
SELECT COALESCE(NULLIF(NULLIF(status, 'UNKNOWN'), 'N/A'), 'missing') AS status
FROM iam.directory.users;