SQL NULL Handling: COALESCE, IFNULL, ISNULL & NVL by Database

Replacing NULL values with meaningful fallback data is one of the most common requirements in database queries and reports. While ANSI SQL defines the portable COALESCE() function, database engines also offer proprietary shortcuts such as IFNULL(), ISNULL(), and NVL().

These functions differ in the number of arguments they accept, how they determine the return data type, whether they short-circuit, and whether they can unexpectedly truncate replacement strings.

Quick reference

The ANSI standard: COALESCE()

All major relational engines (MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle) support COALESCE(). It evaluates its arguments in order from left to right and returns the first non-NULL value. If all arguments evaluate to NULL, COALESCE() returns NULL.

SELECT COALESCE(NULL, NULL, 'Third Value', 'Fourth Value') AS result;

result
-----------
Third Value

Evaluation behavior varies by engine

COALESCE() returns the first non-NULL argument, but do not assume later expressions are always skipped or evaluated only once. PostgreSQL documents that arguments to the right of the first non-NULL value are not evaluated at runtime, although a constant expression such as 1 / 0 can still fail during planning. SQL Server rewrites COALESCE() as CASE and may evaluate an input more than once; a subquery argument can run twice and return different values under READ COMMITTED. See the PostgreSQL conditional expressions documentation and Microsoft’s COALESCE evaluation notes.

Use a simple expression to illustrate the first-non-NULL result:

SELECT COALESCE(NULL, 'First non-NULL', 'Later') AS result;

result
----------------
First non-NULL

MySQL and MariaDB

MySQL and MariaDB support both COALESCE() and IFNULL():

SELECT IFNULL(NULL, 'Default') AS ifnull_result,
       COALESCE(NULL, NULL, 'Default') AS coalesce_result;

ifnull_result | coalesce_result
--------------+----------------
Default       | Default

Avoid the MySQL ISNULL() confusion

In SQL Server, ISNULL(a, b) replaces NULL. In MySQL and MariaDB, ISNULL(expr) is a boolean predicate that accepts only one argument and returns 1 if the expression is NULL, or 0 otherwise:

-- MySQL boolean test:
SELECT ISNULL(NULL) AS is_null_flag,
       ISNULL('Text') AS is_not_null_flag;

is_null_flag | is_not_null_flag
-------------+-----------------
1            | 0

See the MySQL 8.4 COALESCE() reference and SQLiz’s MySQL COALESCE() reference and MariaDB IFNULL() guide.

PostgreSQL

PostgreSQL does not use proprietary names like IFNULL or NVL; it relies entirely on the ANSI SQL standard COALESCE() and NULLIF() expressions.

SELECT COALESCE(user_nickname, user_fullname, 'Anonymous') AS display_name
FROM (VALUES (NULL, 'Jordan Smith')) AS t(user_nickname, user_fullname);

display_name
------------
Jordan Smith

Type compatibility in PostgreSQL

PostgreSQL enforces strong type checking. All arguments in COALESCE() must have compatible data types, or be explicitly cast:

-- Raises an error: integer and text are not implicitly compatible:
-- SELECT COALESCE(NULL, 10, 'N/A');

-- Correct explicit cast:
SELECT COALESCE(NULL, CAST(10 AS text), 'N/A') AS result;

result
------
10

See PostgreSQL’s conditional expressions guide.

SQLite

SQLite supports both coalesce() and ifnull(). ifnull(X, Y) is functionally identical to calling coalesce(X, Y) with two arguments:

SELECT ifnull(NULL, 'Fallback') AS ifnull_result,
       coalesce(NULL, NULL, 'Fallback') AS coalesce_result;

ifnull_result | coalesce_result
--------------+----------------
Fallback      | Fallback

Because SQLite uses dynamic typing (type affinity), coalesce() does not reject arguments of mixed types:

SELECT coalesce(NULL, 100, 'Text') AS result;

result
------
100

See SQLite’s core functions documentation.

SQL Server (Transact-SQL)

SQL Server developers frequently choose between COALESCE() and ISNULL(). While both return a fallback value when the first expression is NULL, they have fundamental semantic differences.

The silent truncation risk in SQL Server ISNULL()

ISNULL(check_expression, replacement_value) returns the exact data type and length of check_expression. If check_expression has a shorter length than replacement_value, SQL Server silently truncates the replacement:

DECLARE @short_code varchar(3) = NULL;

-- ISNULL truncates 'UNKNOWN' to 3 characters:
SELECT ISNULL(@short_code, 'UNKNOWN') AS isnull_output;

isnull_output
-------------
UNK

By contrast, COALESCE() determines its return type using SQL Server’s data type precedence rules, ensuring the longer string length is preserved:

DECLARE @short_code varchar(3) = NULL;

-- COALESCE preserves the full replacement string:
SELECT COALESCE(@short_code, 'UNKNOWN') AS coalesce_output;

coalesce_output
---------------
UNKNOWN

See Microsoft’s COALESCE vs ISNULL documentation and SQLiz’s SQL Server COALESCE() reference.

Oracle

Oracle Database supports ANSI COALESCE() as well as the proprietary NVL() and NVL2() functions.

NVL(expr1, expr2) returns expr2 if expr1 is NULL. NVL2(expr1, expr2, expr3) acts as a ternary operator: if expr1 is not NULL, it returns expr2; if expr1 is NULL, it returns expr3.

SELECT NVL(NULL, 'Default') AS nvl_result,
       NVL2('Present', 'Has Value', 'Empty') AS nvl2_result
FROM dual;

NVL_RESULT | NVL2_RESULT
-----------+------------
Default    | Has Value

Oracle empty string equality to NULL

Unlike other relational databases where '' is a distinct zero-length string, Oracle treats '' as identical to NULL:

SELECT NVL('', 'Replaced Empty String') AS result FROM dual;

RESULT
---------------------
Replaced Empty String

See Oracle’s NVL manual and SQLiz’s Oracle COALESCE() reference.

Which function should you use?

1. Default to COALESCE() for portability: Because COALESCE() is ANSI SQL compliant and available across every database engine, queries remain portable during migrations.
2. Watch for SQL Server type truncation: In SQL Server, avoid ISNULL() when the fallback literal or expression is longer than the column definition.
3. Remember argument limits: IFNULL() (MySQL, SQLite) and NVL() (Oracle) only accept two arguments. When evaluating a fallback chain of three or more values, always use COALESCE().