IFNULL()

Syntax

How it works

IFNULL(a, b) is a compact conditional: if a is not NULL it returns a, and if a is NULL it returns b. It always takes exactly two arguments and is the standard NULL replacement function in MySQL and SQLite.

The catch is portability, because the same idea has a different name in almost every engine. MySQL and SQLite spell it IFNULL(a, b). SQL Server spells it ISNULL(a, b). Oracle spells it NVL(a, b). There is no IFNULL in SQL Server or Oracle, so copying MySQL code straight across will fail. The one name that works everywhere is the ANSI-standard COALESCE(), so COALESCE(a, b) is the recommended, portable choice.

Beyond portability, COALESCE() is also more capable: it accepts two or more arguments and returns the first non-NULL one, whereas IFNULL, ISNULL and NVL are strictly two-argument. There is one subtle trap in SQL Server: the result type and length of ISNULL follow the FIRST argument, which can silently truncate the replacement, while COALESCE uses full data type precedence across all arguments. To understand when values become NULL in the first place, see SQL NULL values explained.

Examples

Default for a NULL column in each engine

-- Same intent, four spellings. Replace a NULL discount with 0.
-- MySQL / SQLite:
SELECT IFNULL(discount, 0)   AS discount FROM orders;
-- SQL Server:
SELECT ISNULL(discount, 0)   AS discount FROM orders;
-- Oracle:
SELECT NVL(discount, 0)      AS discount FROM orders;
-- Portable, works everywhere:
SELECT COALESCE(discount, 0) AS discount FROM orders;

discount
--------
      15
       0
       5
       0

Display 'N/A' for missing data

-- phone is NULL for customers who never gave one
SELECT name,
       IFNULL(phone, 'N/A') AS phone
FROM customers;

name  | phone
------+-----------
 Sara | 0500123456
 Omar | N/A
 Lina | 0555987654

Guard an aggregate that can return NULL

-- SUM() returns NULL, not 0, when no rows match.
-- IFNULL turns an empty result into a clean 0.
SELECT IFNULL(SUM(amount), 0) AS total_paid
FROM payments
WHERE customer_id = 42;

total_paid
----------
         0

Fall back through several columns with COALESCE

-- IFNULL only takes two arguments; COALESCE takes many.
-- Use the first contact method that is present.
SELECT COALESCE(mobile, landline, email, 'no contact') AS contact
FROM customers;

contact
-----------------
 0500123456
 info@site.com
 no contact

The SQL Server ISNULL truncation surprise

-- expression is CHAR(1); ISNULL forces the result to CHAR(1),
-- so the replacement 'unknown' is truncated to 'u'.
SELECT ISNULL(grade, 'unknown')   AS isnull_result,
       COALESCE(grade, 'unknown') AS coalesce_result
FROM students;

isnull_result | coalesce_result
--------------+----------------
 A            | A
 u            | unknown

Common mistakes

-- IFNULL does not exist in SQL Server; this errors out
-- Msg 195: 'IFNULL' is not a recognized built-in function name
SELECT IFNULL(discount, 0) FROM orders;

-- Use ISNULL in SQL Server, or COALESCE anywhere
SELECT ISNULL(discount, 0)   FROM orders;   -- SQL Server
SELECT COALESCE(discount, 0) FROM orders;   -- portable

-- ISNULL result type follows the FIRST argument.
-- If code is VARCHAR(3), 'unknown' is silently cut to 'unk'
SELECT ISNULL(code, 'unknown') FROM parts;

-- COALESCE uses type precedence across all arguments,
-- so the full replacement text survives
SELECT COALESCE(code, 'unknown') FROM parts;

-- IFNULL, ISNULL and NVL take EXACTLY two arguments
SELECT IFNULL(mobile, landline, 'N/A') FROM customers;

-- For more than one fallback, use COALESCE
SELECT COALESCE(mobile, landline, 'N/A') FROM customers;

Performance

IFNULL() and its cousins are cheap scalar functions evaluated per row, so the function call itself is rarely a bottleneck. The real cost comes from wrapping an indexed column in the function inside a WHERE clause, such as WHERE IFNULL(status, 0) = 0, which is not sargable and forces a full scan. Rewrite the predicate as WHERE status = 0 OR status IS NULL so the index can be used.

Prefer COALESCE() over engine-specific names in portable code; it compiles to the same efficient conditional and avoids the SQL Server ISNULL truncation issue. In hot paths it is usually better to store a sensible default at write time than to compute a replacement on every read.

Interview questions

SELECT COALESCE(mobile, landline, email, 'N/A') AS contact
FROM customers;