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.