COALESCE Function
SQL · Conditional Expressions
Theory
COALESCE(a, b, c, ...) returns the first argument that is not NULL, reading from left to right. COALESCE(nickname, first_name, 'Unknown') shows the nickname if there is one, otherwise the first name, and 'Unknown' only if both are missing. If every argument is NULL, the result is NULL. It is part of standard SQL and works in every major engine.
Common uses are display defaults, treating missing numbers as zero (COALESCE(discount, 0)), and choosing the best available value from several columns or joined tables. It is shorthand for a CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ... END. Arguments should share a compatible type. Engine-specific two-argument versions exist, such as IFNULL (MySQL, SQLite), ISNULL (SQL Server) and NVL (Oracle), but COALESCE is the portable choice.
Why we use it
NULLs make reports show blanks and make arithmetic return NULL (amount + NULL is NULL). COALESCE swaps in a sensible fallback so outputs and calculations stay readable and correct.
When to use it
Use COALESCE to replace NULLs with defaults or pick the first available value. To filter NULL rows out instead of replacing them, use IS NULL / IS NOT NULL. For conditions other than NULL, use CASE.
How it works
- Take the first argument
- Is it NOT NULL? Yes: Return it and stop. No: Move to the next argument.
- Any arguments left? Yes: Check that argument the same way. No: Return NULL.
- Result is the first non-NULL value
Syntax
COALESCE(value1, value2, ...)Example
SELECT COALESCE(nickname, first_name, 'Unknown') FROM employees;Key points
- Great for providing fallback/default values
- Accepts any number of arguments, checked left to right
- Returns NULL only if every argument is NULL
- Portable alternative to IFNULL, ISNULL and NVL
- COALESCE(col, 0) keeps arithmetic from turning NULL