NULLIF
NULLIF returns NULL if two expressions are equal, and otherwise returns the first expression -- commonly used to avoid divide-by-zero errors or convert sentinel values into NULL.
Overview
NULLIF(expr1, expr2) compares two expressions: if they're equal, it returns NULL; otherwise, it returns expr1. It's shorthand for CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END, and it's the conceptual inverse of COALESCE, which replaces NULL with a value instead of replacing a value with NULL.
Copy code
SELECT NULLIF(5, 5); -- NULL
SELECT NULLIF(5, 3); -- 5
Avoiding divide-by-zero errors
The most common real-world use of NULLIF is preventing division errors by turning a zero denominator into NULL, which propagates cleanly instead of raising an error:
Copy code
SELECT
revenue,
clicks,
revenue / NULLIF(clicks, 0) AS revenue_per_click
FROM campaign_stats;
When clicks is 0, NULLIF(clicks, 0) returns NULL, and revenue / NULL evaluates to NULL rather than throwing a division-by-zero error -- letting the query keep running and letting downstream logic (e.g. COALESCE(..., 0)) decide how to display it.
Converting sentinel values to NULL
Legacy systems sometimes use sentinel values like -1, 'N/A', or 'unknown' to represent missing data instead of a real NULL. NULLIF converts these into proper NULLs so they're excluded from aggregates and treated consistently:
Copy code
SELECT NULLIF(status_code, -1) AS status_code
FROM legacy_events;
Combining with COALESCE
NULLIF and COALESCE are often chained together -- for example, normalizing a sentinel value to NULL and then substituting a friendlier default:
Copy code
SELECT COALESCE(NULLIF(status_code, -1), 0) AS status_code
FROM legacy_events;
DuckDB notes
NULLIF in DuckDB follows the ANSI SQL standard exactly; no DuckDB-specific behavior differs from other engines.
Related terms
COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns.
NULL →NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.
CASE expression →The CASE expression evaluates a list of conditions and returns a corresponding value for the first one that's true, functioning as SQL's if/else logic inside a query.
GREATEST and LEAST →GREATEST and LEAST return the largest or smallest value, respectively, among a list of expressions passed as arguments.
TRY_CAST →TRY_CAST attempts to convert a value to another data type and returns NULL instead of raising an error if the conversion fails.
IN operator →The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.
FAQS
NULL if a equals b, otherwise it returns a unchanged.
Wrapping the denominator in NULLIF(denominator, 0) turns a zero into NULL before the division happens, so the result becomes NULL instead of raising a division-by-zero error.
In spirit, yes -- COALESCE replaces NULL values with a fallback, while NULLIF replaces a specific matching value with NULL.
