SQL COALESCE and NULLIF: make missing data explicit

COALESCE(city, 'Unknown') gives an export a visible label without deleting rows. COALESCE(SUM(salary), 0) gives a zero total for an empty team after a LEFT JOIN.

NULLIF(budget, 0) prevents division by zero by returning NULL when the budget is zero. Decide whether zero or unknown is the honest output for each metric.

Worked example

Export every customer with a usable city label.

SELECT name, COALESCE(city, 'Unknown') AS city
FROM customers
ORDER BY name, id
LIMIT 8;

Common mistakes

• Replacing unknown values with zero when zero has a different business meaning.
• Forgetting a LEFT JOIN, which removes empty groups before COALESCE can help.