Learn how SQL COALESCE works with practical examples. Handle NULL values, set defaults, and master COALESCE vs ISNULL vs IFNULL across all databases.
Jump into the SQLNoir case files and put these tips to work.
COALESCE returns the first non-NULL value from a list of arguments. Simple concept, but knowing when and why to use it separates beginners from pros.
COALESCE takes multiple arguments and returns the first one that isn't NULL. If every argument is NULL, it returns NULL. Think of it like following leads in an investigation: check the primary witness first. Dead end? Try the next lead. Keep going until you find something useful.
-- Returns 'Found it!' (first non-NULL value) SELECT COALESCE(NULL, NULL, 'Found it!', 'Too late'); -- Returns the first available contact method SELECT COALESCE(witness_statement, anonymous_tip, 'No leads') FROM case_files;
COALESCE evaluates arguments left to right, stopping at the first non-NULL value
COALESCE(witness_statement,anonymous_tip,'No leads')Anatomy of a COALESCE expression
“I know exactly what coalesce does and I have used it once. Literally, once. What I don't understand is why I would want to replace a null value with zero instead of just letting it be null in the data.”
Fair question. Here's why NULLs cause real problems:
-- Detective pay report WITHOUT COALESCE SELECT name, salary + bonus AS total_pay FROM detectives; -- Results: -- Sarah Chen | 85000 (salary 75000 + bonus 10000) -- Mike Torres | NULL (salary 80000 + bonus NULL) -- Jess Park | NULL (salary 70000 + bonus NULL)
•NULL + anything = NULL (Mike's pay vanishes)
•2 of 3 detectives show no total pay
•SUM(total_pay) only counts Sarah's row
-- Detective pay report WITH COALESCE SELECT name, salary + COALESCE(bonus, 0) AS total_pay FROM detectives; -- Results: -- Sarah Chen | 85000 (75000 + 10000) -- Mike Torres | 80000 (80000 + 0) -- Jess Park | 70000 (70000 + 0)
•NULL bonus treated as 0
•All detectives show accurate total pay
•SUM and AVG calculations work correctly
NULL arithmetic is the #1 reason you need COALESCE
NULLs also cause surprises in filtering, sorting, and aggregation. COUNT(*) counts all rows, but COUNT(bonus) skips NULLs silently. AVG ignores NULL rows entirely, which can skew your results. COALESCE lets you decide what a missing value means instead of letting the database decide for you.
The same NULL handling skills come up constantly when querying real databases. SQLNoir's detective cases use multiple tables with LEFT JOINs, missing witness statements, and incomplete records that make pattern matching essential.
COALESCE(value1, value2, value3, ..., valueN)
Takes 2 or more arguments. Returns the first non-NULL value, or NULL if all arguments are NULL.
COALESCE uses short-circuit evaluation. It checks arguments left to right and stops the moment it finds a non-NULL value. The remaining arguments are never evaluated.
-- Two arguments (most common) SELECT COALESCE(phone, 'No phone on file') FROM suspects; -- Three arguments (fallback chain) SELECT COALESCE(mobile, work_phone, 'No contact') FROM witnesses; -- Four arguments (extended fallback) SELECT COALESCE(email, mobile, office_phone, 'Unreachable') FROM persons_of_interest;
COALESCE(a, b, c) is equivalent to CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END. Same result, less typing. If you already know CASE WHEN, you already understand the logic behind COALESCE.One important rule: all arguments must have compatible data types. You can't mix integers and strings without an explicit CAST. The database uses the data type with the highest precedence from the argument list.
“What are some really good examples of using coalesce? I'd like to know best practices of when it is most useful to use it particularly in combination with joins”
-- Clean report output: no ugly NULLs
SELECT
name,
COALESCE(phone, 'No phone on file') AS contact_number,
COALESCE(email, 'No email provided') AS email_address
FROM suspects;Replace NULL with human-readable defaults for reports and dashboards.
-- Prevent NULL from breaking calculations
SELECT
case_id,
COALESCE(physical_evidence_count, 0)
+ COALESCE(digital_evidence_count, 0)
+ COALESCE(witness_count, 0) AS total_leads
FROM cases;Wrap any column that might be NULL with COALESCE(column, 0) before doing arithmetic.
-- Try multiple contact methods in priority order
SELECT
name,
COALESCE(mobile, work_phone, home_phone, 'No contact info')
AS best_contact
FROM witnesses;Check mobile first. If NULL, try work phone. If NULL, try home phone. If all NULL, show a default message.
-- Handle unmatched rows from LEFT JOIN
SELECT
cs.location,
cs.description,
COALESCE(s.name, 'No suspect identified') AS suspect
FROM crime_scenes cs
LEFT JOIN suspects s ON cs.suspect_id = s.id;LEFT JOINs produce NULLs for unmatched rows. COALESCE turns those NULLs into meaningful values.
-- Push NULL priorities to the bottom SELECT case_id, title, priority FROM cases ORDER BY COALESCE(priority, 999);
Without COALESCE, NULL priority rows sort unpredictably (varies by database). COALESCE(priority, 999) pushes them to the end.
A body at Coral Bay Marina. Multiple tables, missing records, incomplete witness statements. Can you piece together the evidence?
Start InvestigationEvery major database has its own NULL-handling function. COALESCE is the only one that works everywhere.
| Feature | COALESCE | ISNULL | IFNULL | NVL |
|---|---|---|---|---|
| SQL Standard | ✅ Yes | ❌ No | ❌ No | ❌ No |
| Arguments | 2+ | 2 only | 2 only | 2 only |
| Database | All | SQL Server | MySQL | Oracle |
| Type coercion | Highest precedence | First arg type | First arg type | First arg type |
| Short-circuit | ✅ Yes | N/A (2 args) | N/A (2 args) | N/A (2 args) |
| Portability | Excellent | Poor | Poor | Poor |
NULL handling functions across databases
-- SQL Standard (works everywhere) SELECT COALESCE(phone, 'Unknown') FROM suspects; -- SQL Server only SELECT ISNULL(phone, 'Unknown') FROM suspects; -- MySQL only SELECT IFNULL(phone, 'Unknown') FROM suspects; -- Oracle only SELECT NVL(phone, 'Unknown') FROM suspects;
Same result, but only COALESCE is portable. If you switch databases, COALESCE keeps working.
SELECT COALESCE(age, 'Unknown') FROM suspects; -- ERROR: cannot convert 'Unknown' to integer
•age is an integer column
•'Unknown' is a string
•COALESCE requires compatible types
SELECT COALESCE(CAST(age AS VARCHAR), 'Unknown') FROM suspects;
•Both arguments are now VARCHAR
•Returns age as string or 'Unknown'
All COALESCE arguments must share compatible data types
-- Empty string is NOT NULL
SELECT COALESCE('', 'Fallback');
-- Returns '' (empty string), NOT 'Fallback'
-- To treat empty strings as missing:
SELECT COALESCE(NULLIF(notes, ''), 'No notes on file')
FROM case_files;NULLIF(notes, '') converts empty strings to NULL first, then COALESCE handles the fallback.
COALESCE shows up in SQL interviews more often than you'd expect. Here are the questions that come up most:
SELECT
e.name AS employee,
COALESCE(m.name, 'No Manager') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Classic self-join + COALESCE combo. The LEFT JOIN produces NULLs for employees without managers, and COALESCE replaces them.
Three key differences: COALESCE is SQL standard (works everywhere), takes 2+ arguments, and uses highest-precedence type coercion. ISNULL is SQL Server-only, takes exactly 2 arguments, and uses first-argument type coercion (which can silently truncate data).
Q1.What does COALESCE(NULL, NULL, 42, NULL) return?
Q2.How many arguments can COALESCE take?
Q3.What happens with COALESCE(integer_col, 'N/A')?
Q4.COALESCE(a, b) is equivalent to which CASE expression?
Ready to put NULL handling into practice?
COALESCE shows up constantly in real SQL work, from cleaning reports to fixing JOIN results. SQLNoir gives you 6 free detective mysteries where NULL handling, JOINs, and data gaps are part of every investigation.
Start Your Investigation →COALESCE is SQL standard (works on every database), accepts 2 or more arguments, and uses highest-precedence type coercion. ISNULL is SQL Server-only, takes exactly 2 arguments, and uses the first argument's data type (which can silently truncate data). Always prefer COALESCE for portability.
Yes. COALESCE(a, b, c, d, ...) checks each argument left to right and returns the first non-NULL value. Unlike ISNULL, IFNULL, and NVL which are limited to exactly 2 arguments, COALESCE has no practical limit.
NULL. If every argument is NULL, COALESCE returns NULL. To guarantee a non-NULL result, add a literal default as the last argument: COALESCE(col1, col2, 'default value').
Functionally equivalent. COALESCE(a, b) produces the same result as CASE WHEN a IS NOT NULL THEN a ELSE b END. COALESCE is shorter and more readable, especially with multiple arguments.
Yes, but an empty string ('') is NOT NULL. COALESCE treats empty strings as non-NULL values and returns them. If you need to treat empty strings as missing, wrap the column in NULLIF first: COALESCE(NULLIF(column, ''), 'default').