Skip to content

NULL handling · Easy

How do COALESCE and NULLIF help when handling NULLs and division by zero?

Short answer

COALESCE returns the first non-NULL argument, so it supplies defaults, for example labelling a missing referral source as 'Unknown' or treating no sales as zero. NULLIF(a, b) returns NULL when a equals b, which is mainly used as NULLIF(denominator, 0) so that division by zero produces NULL instead of an error or a misleading result.

Detailed answer

COALESCE for readable reporting:

SELECT COALESCE(referral_source, 'Unknown') AS referral_source,
       COUNT(*) AS customers
FROM customers
GROUP BY 1
ORDER BY customers DESC;

NULLIF for safe rates. Checkout-to-purchase conversion by traffic source — some sources may have no checkouts at all:

SELECT traffic_source,
       SUM(started_checkout) AS checkouts,
       SUM(purchased)        AS purchases,
       ROUND(100.0 * SUM(purchased) / NULLIF(SUM(started_checkout), 0), 1) AS checkout_conversion_pct
FROM web_sessions
GROUP BY traffic_source
ORDER BY checkout_conversion_pct DESC;

PostgreSQL and SQL Server raise an error on division by zero, and SQLite returns NULL; NULLIF makes the intent explicit everywhere.

Judgement call: do not reflexively wrap everything in COALESCE(x, 0). A missing value is not the same as zero — replacing NULL delivery dates or NULL ratings with 0 distorts averages. Use COALESCE for display labels and for genuine "nothing happened" counts, and keep NULLs where the value is truly unknown.