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

Short-circuit evaluation

Standard COALESCE() implementations short-circuit: once a non-NULL argument is found, subsequent expressions are not evaluated. This prevents unnecessary function execution or subqueries:

-- The division by zero is never executed because the first argument is not NULL:
SELECT COALESCE('Valid', 1 / 0) AS safe_result;

safe_result
-----------
Valid

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().