SQLite ifnull(): Replace NULL Values (Examples)

SQLite ifnull(X, Y) returns X when it is not NULL; otherwise it returns Y. If both arguments are NULL, the result is NULL. Use it to provide a fallback for missing values, not to test whether a value is false, zero or empty.

Syntax

ifnull(X, Y)

ifnull() requires exactly two arguments. It is equivalent to coalesce(X, Y) with two arguments; coalesce() can accept more than two.

Replace a NULL value

SELECT
    ifnull(NULL, 'unknown') AS missing,
    ifnull('Ada', 'unknown') AS present,
    ifnull(NULL, NULL) AS both_missing;

missing  present  both_missing
-------  -------  ------------
unknown  Ada      NULL

The fallback is used only when the first expression evaluates to SQL NULL.

Empty strings and zero are not NULL

An empty string ('') and the number 0 are actual values, so ifnull() leaves them unchanged:

SELECT
    ifnull('', 'fallback') AS empty_text,
    ifnull(0, 99) AS zero_value,
    ifnull(nullif('', ''), 'fallback') AS blank_as_missing;

empty_text  zero_value  blank_as_missing
----------  ----------  ----------------
            0           fallback

To treat an empty string as missing, first convert it to NULL using nullif(). This distinction matters for form inputs and imported CSV data.

Use ifnull() with table columns

For example, show a zero discount when the column is NULL:

WITH products(name, discount) AS (
    VALUES ('A', NULL), ('B', 0), ('C', 5)
)
SELECT name, ifnull(discount, 0) AS discount
FROM products;

name  discount
----  --------
A     0
B     0
C     5

This changes the displayed result, not the stored value in the table.

Be careful when replacing NULL before AVG

SQLite’s avg() ignores NULL inputs. Replacing them with zero changes the denominator:

WITH prices(value) AS (
    VALUES (10), (NULL), (20)
)
SELECT
    avg(value) AS ignore_missing,
    avg(ifnull(value, 0)) AS zero_fill
FROM prices;

ignore_missing  zero_fill
--------------  ---------
15.0            10.0

Use ifnull(value, 0) in an average only when a missing value should genuinely count as zero. Otherwise, keep the NULL and let avg() ignore it.

ifnull() vs coalesce()

Use ifnull(X, Y) for a single fallback. Use coalesce(X, Y, Z) when you want to try multiple values in order. Both functions return the first non-NULL argument.

For the exact function contract, see the official SQLite ifnull() reference.

Try these examples online

Run and modify the queries in the SQLite Playground, using a temporary database in your browser.