COALESCE vs ISNULL vs IFNULL vs NVL: Handling NULL in SQL

cover

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 is VARCHAR(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

FunctionDatabasesArgumentsNotes
COALESCEAll major databases2 or moreStandard, short-circuits
ISNULLSQL Server2Type of the first argument
ISNULLMySQL1Returns 1 or 0
IFNULLMySQL, MariaDB, SQLite2Simple replacement
NVLOracle2Evaluates 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