COALESCE vs ISNULL vs IFNULL vs NVL: Handling NULL in SQL
Every SQL database has its own way of replacing NULL values, and some of the functions behave very differently even when they share a name. Here is a clear comparison.
The short answer
Use COALESCE. It is part of the SQL standard, it works in MySQL, MariaDB, PostgreSQL, SQL Server, Oracle and SQLite, and it accepts any number of arguments. Use the vendor-specific functions only when you have a specific reason.
COALESCE (standard SQL)
SELECT COALESCE(mobile_phone, work_phone, home_phone, 'No phone') AS phone
FROM customers;
Returns the first non-NULL value in the list. It stops evaluating as soon as it finds one.
ISNULL: same name, different behavior
- SQL Server:
ISNULL(price, 0)replaces NULL with 0. It takes exactly two arguments, and the result uses the data type of the first argument. That can silently truncate the replacement value. For example, if the column isVARCHAR(3),ISNULL(col, 'Unknown')returns'Unk'. COALESCE doesn't have this problem. - MySQL:
ISNULL(price)takes one argument and returns 1 or 0. It is a test, not a replacement. Code ported from SQL Server breaks here.
IFNULL (MySQL, MariaDB, SQLite)
SELECT IFNULL(discount, 0) FROM orders;
A two-argument shortcut that behaves like a two-argument COALESCE.
NVL (Oracle)
SELECT NVL(commission, 0) FROM employees;
Oracle's two-argument version. One important difference: NVL evaluates both arguments every time, while COALESCE stops at the first non-NULL value. If the second argument is an expensive function or subquery, COALESCE can be faster. Oracle also has NVL2(expr, value_if_not_null, value_if_null).
Comparison table
| Function | Databases | Arguments | Notes |
|---|---|---|---|
| COALESCE | All major databases | 2 or more | Standard, short-circuits |
| ISNULL | SQL Server | 2 | Type of the first argument |
| ISNULL | MySQL | 1 | Returns 1 or 0 |
| IFNULL | MySQL, MariaDB, SQLite | 2 | Simple replacement |
| NVL | Oracle | 2 | Evaluates both arguments |
A note on empty strings
None of these functions treat an empty string as NULL, except in Oracle, where an empty string is NULL. If you need to catch both, combine COALESCE with NULLIF: COALESCE(NULLIF(name, ''), 'Anonymous').
Comments
Post a Comment