The COALESCE function in SQL is used to handle NULL values. It returns the first non-null value from a list of expressions. This is really useful when working with incomplete or missing data because it allows us to replace NULLs with a default or fallback value.
For example, let’s say we have a Customers table with columns like Email, Phone, and Address. If I want to contact customers but some of them don’t have an email, I can use COALESCE like this:
SELECT CustomerID,
COALESCE(Email, Phone, Address, 'No Contact Info') AS ContactInfo
FROM Customers;
In this query, COALESCE checks each column in order — if Email is NULL, it checks Phone, then Address, and if all are NULL, it finally returns 'No Contact Info'.
I used COALESCE in a sales data reporting project. In one of the reports, the revenue column sometimes had NULL values when data wasn’t yet loaded. Instead of showing blanks, I used COALESCE(Revenue, 0) so that missing values appeared as 0. It made calculations and summaries much more reliable and avoided issues in aggregations like SUM() or AVG().
A common challenge is when people confuse COALESCE with ISNULL or NVL (in Oracle). COALESCE is actually more powerful because it can take multiple arguments and works consistently across different databases.
One limitation is that all arguments must be of the same data type or compatible types; otherwise, it can throw conversion errors.
An alternative in some cases is using CASE statements, but COALESCE is shorter, cleaner, and easier to maintain — so I generally prefer it whenever I’m handling NULL-related logic in queries.