A NULL value propagating through a calculation or a displayed report is rarely what's wanted. SQL's NULL-substitution functions solve exactly that: return a fallback value whenever the original is NULL, otherwise return the original value unchanged.
Learning Objectives
- Use COALESCE, the ANSI-standard, portable option.
- Recognize IFNULL, ISNULL, and NVL as database-specific alternatives.
- Choose COALESCE by default for anything that needs to run on more than one database.
COALESCE: The Portable Choice
COALESCE(value, fallback) returns value unless it's NULL, in which case it returns fallback. It's part of the ANSI SQL standard and works identically on every database in this course, making it the right default choice whenever portability matters at all.
SELECT first_name, COALESCE(bonus, 0) AS bonus FROM employees;
COALESCE Accepts More Than Two Arguments
COALESCE checks its arguments left to right and returns the first one that isn't NULL, falling through multiple fallbacks in priority order. The database-specific functions below only ever accept exactly two.
SELECT COALESCE(mobile_phone, office_phone, 'No phone on file') AS contact_number FROM employees;
Database-Specific Equivalents
| Function | Databases | Arguments |
|---|---|---|
| COALESCE(a, b, ...) | All six | Any number |
| IFNULL(a, b) | MySQL / MariaDB / SQLite | Exactly two |
| ISNULL(a, b) | SQL Server | Exactly two |
| NVL(a, b) | Oracle | Exactly two |
SQL Server's ISNULL has nothing to do with the IS NULL comparison operator from Module 1, despite the similar name, ISNULL(a, b) substitutes a fallback value; IS NULL tests whether a value is missing. Don't confuse the two just because they sound alike.
Try It Yourself
Write a portable query that shows each employee's manager's name, or 'No manager' if manager_id is NULL. (Hint: this needs the self join from Module 4.)
SELECT e.first_name, COALESCE(m.first_name, 'No manager') AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
Frequently Asked Questions
Is there ever a reason to use IFNULL or ISNULL instead of COALESCE?
Mostly brevity when a query is known to only ever run on one specific database. For anything shared, reused across environments, or copied from documentation, COALESCE avoids the whole question.
Does COALESCE work with mixed data types?
The arguments need compatible types, the same rule as CASE and UNION. COALESCE(numeric_column, 'text fallback') typically errors or forces an implicit conversion, depending on the database, not something to rely on.
Common Beginner Mistakes
Using IFNULL on SQL Server, or ISNULL on MySQL
Neither function exists on the other database, this is one of the clearest database-specific-syntax errors in this course. COALESCE sidesteps the problem entirely by working on both.
Confusing ISNULL(column, fallback) with the column IS NULL comparison
ISNULL is a two-argument function that substitutes a value. IS NULL is a comparison operator, from Module 1, that tests true or false. Similar name, genuinely different purpose.
Best Practices
- Default to COALESCE everywhere, it's standard, portable, and accepts any number of fallback values.
- Reach for a database-specific function only when a query is deliberately scoped to one database and brevity matters.
- Keep ISNULL and IS NULL straight, a function call vs a comparison operator, despite the similar spelling.
Interview Questions
Why is COALESCE generally preferred over IFNULL or ISNULL?
COALESCE is part of the ANSI SQL standard and works identically across every major database, while IFNULL (MySQL/MariaDB/SQLite), ISNULL (SQL Server), and NVL (Oracle) are each specific to one. COALESCE also accepts any number of fallback arguments, evaluated left to right, where the others accept only two.
Write a portable query that returns each employee's bonus, substituting 0 when it's NULL, without using a database-specific function.
SELECT first_name, COALESCE(bonus, 0) AS bonus FROM employees; — COALESCE(value, fallback) is the ANSI-standard choice, and runs unchanged on MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle alike.
Summary
COALESCE(a, b, ...) returns the first non-NULL argument, works identically everywhere, and accepts any number of fallbacks. IFNULL, ISNULL, and NVL do the same job for exactly two arguments, but each only on its own specific database, COALESCE is the right default whenever portability matters.
What's Next?
Stored procedures next, reusable named routines stored directly in the database, with one of the largest syntax splits of any topic in this course.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.