NULLIF

Returns NULL when its two arguments are equal, otherwise the first argument.

Syntax

NULLIF(value, value_to_treat_as_null)

NULLIF(a, b) is shorthand for CASE WHEN a = b THEN NULL ELSE a END.

Safe division: x / NULLIF(y, 0) returns NULL instead of raising division by zero. Wrap in COALESCE(..., 0) only if 0 is a meaningful answer.
Cleaning: turn placeholder values into real NULLs: NULLIF(trim(city), ''), NULLIF(code, 'N/A').
The result type is the type of the first argument.

Examples

Safe ratio starter

SELECT category,
       sum(stock) AS units_in_stock,
       count(*) FILTER (WHERE stock < 10) AS low_stock_products,
       round(100.0 * count(*) FILTER (WHERE stock < 10) / NULLIF(count(*), 0), 1) AS pct_low
FROM products
GROUP BY category
ORDER BY category;

If a group had zero rows the ratio would be NULL instead of an error.

Blank strings to NULL starter

SELECT NULLIF(trim('   '), '') AS cleaned_blank,
       NULLIF(trim(' Khobar '), '') AS cleaned_value;

The blank becomes NULL; real text passes through trimmed.

Average time to first response, ignoring zero-minute outliers saas

SELECT priority,
       round(avg(NULLIF(extract(epoch FROM first_response_at - opened_at) / 60, 0))::numeric, 1) AS avg_minutes
FROM support_tickets
WHERE first_response_at IS NOT NULL
GROUP BY priority
ORDER BY priority;

NULLIF turns zero durations into NULL, which AVG then skips.

Pitfalls

Reversing the arguments: NULLIF(0, y) returns 0 or NULL, not y.
Forgetting that the safe-division result is NULL, which then disappears from SUM/AVG.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

BigQuery: SAFE_DIVIDE(x, y) returns NULL on division by zero.
Snowflake: DIV0(x, y) returns 0 and DIV0NULL(x, y) returns 0 when y is 0 or NULL.