Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL IFNULL(), NVL() and ISNULL()

Reference for the vendor versions of COALESCE: IFNULL in SQLite and MySQL, NVL in Oracle, ISNULL in SQL Server, and why COALESCE is the portable choice.

Every database needs a way to say "use this value if that one is NULL", and before COALESCE was standardized each vendor invented its own two-argument function: IFNULL (MySQL, SQLite), NVL (Oracle), and ISNULL (SQL Server). They all do the same basic job: return the first argument unless it is NULL, in which case return the second.

New code should prefer COALESCE, which is standard and works everywhere. This page helps you read existing code and translate it. Beware that ISNULL also exists in MySQL with a different meaning.

SQL
SELECT IFNULL(email, 'unknown') FROM employees;   -- SQLite, MySQL

Try it

SQL IFNULL and COALESCE give the same result ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Replacing NVL or ISNULL with CASE (portable and explicit) ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Only the first argument is tested ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
IFNULL(expr, replacement)      -- SQLite, MySQL
NVL(expr, replacement)         -- Oracle
ISNULL(expr, replacement)      -- SQL Server
COALESCE(expr, replacement)    -- standard, all databases
Part Description
expr The value to test.
replacement Returned when expr is NULL.

Returns: expr if it is not NULL, otherwise replacement.

Dialect differences

Database Function Notes
SQLite IFNULL(a, b) Same as COALESCE(a, b) with two arguments.
MySQL IFNULL(a, b) Result type is the common type of both arguments. ISNULL(x) takes one argument and returns 1 or 0.
PostgreSQL none Use COALESCE. (ISNULL is a non-standard postfix test, not a function.)
SQL Server ISNULL(a, b) The result takes the type and length of the first argument, so ISNULL(varchar_5_column, 'long text') is truncated to 5 characters. COALESCE does not truncate.
Oracle NVL(a, b), NVL2(a, if_not_null, if_null) Evaluates both arguments; COALESCE short-circuits. The empty string is NULL.
SQL
-- Oracle
SELECT NVL(commission, 0) FROM employees;
SELECT NVL2(commission, 'paid', 'none') FROM employees;

-- SQL Server
SELECT ISNULL(middle_name, '') FROM people;

-- MySQL: one-argument test, not a default
SELECT ISNULL(email) FROM employees;     -- 1 or 0

Usage notes

  • Prefer COALESCE. It is standard, accepts any number of arguments and short-circuits.
  • Truncation in SQL Server. ISNULL coerces the replacement to the first argument's type; this is the most common bug when porting from COALESCE.
  • Only NULL is replaced. Empty strings and zeros pass through unchanged.
  • Oracle's NVL2 has three arguments and distinguishes "has a value" from "has none"; the portable form is CASE WHEN x IS NOT NULL THEN a ELSE b END.
  • Performance. Wrapping an indexed column in any of these in a WHERE clause usually disables the index.
  • Name clash. A bare ISNULL in MySQL tests for NULL; in SQL Server it replaces. Check which database you are in.