SQL COALESCE: Handle NULLs Gracefully
COALESCE returns the first non-NULL value from a list. Essential for default values, NULL-safe calculations, and clean query output.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Show the customer's nickname if available, otherwise their first name, otherwise 'Unknown'"
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM customers;COALESCE checks each argument left to right. If nickname is NULL, it tries first_name. If that's also NULL, it returns 'Unknown'. The first non-NULL value wins.
Step-by-step breakdown
- COALESCE(value1, value2, ..., default) returns first non-NULL
- nickname is checked first — used if not NULL
- first_name is the fallback if nickname is NULL
- 'Unknown' is the final default if both are NULL
- Works with any data type: text, numbers, dates
Edge cases & warnings
- Empty string ('') is NOT NULL — COALESCE won't skip it
- All arguments should be the same data type or implicitly castable
- COALESCE is standard SQL — works in PostgreSQL, MySQL, SQL Server, SQLite, Oracle
- COALESCE(a, b) is equivalent to CASE WHEN a IS NOT NULL THEN a ELSE b END
Examples
"nickname: 'Johnny', first_name: 'John'"
Johnny"nickname: NULL, first_name: 'John'"
John"nickname: NULL, first_name: NULL"
UnknownFrequently asked questions
What's the difference between COALESCE and IFNULL?
IFNULL takes exactly 2 arguments and is MySQL-specific. COALESCE takes any number of arguments and is ANSI SQL standard. Always prefer COALESCE for portability.
How do I handle NULLs in calculations?
Wrap columns: COALESCE(amount, 0) ensures NULL amounts become 0 in SUM/AVG calculations. NULL + anything = NULL without COALESCE.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.