COALESCE returns the first non-NULL expression from a list of expressions.
OUTER JOINs frequently produce NULL values for missing related records, and reports often need a meaningful replacement.
Show zero sales instead of NULL for customers without orders.
SELECT
c.CustomerName,
COALESCE(SUM(o.OrderAmount), 0) AS TotalSales
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;Used in financial reports, dashboards, customer summaries, and data presentation.
- COALESCE replaces NULL with the first available value.
- It is useful after LEFT and FULL OUTER JOINs.
- Zero can be used for missing numeric totals.
- The replacement value should match the business meaning.
- NULL and zero are not always logically equivalent.
Why use COALESCE after a LEFT JOIN?
It can replace NULL values from missing related records with a meaningful default.
Are NULL and zero the same?
No. NULL means unknown or missing, while zero is an actual numeric value.