How SQLite Ifnull() Works

The SQLite ifnull() function allows you to replace NULL values with another value.

It takes two arguments, and it returns a copy of its first non-NULL argument, or NULL if both arguments are NULL.

The ifnull() function is equivalent to coalesce() with two arguments.

Simple Example

Here’s a simple example to demonstrate the concept.

SELECT 
  ifnull(123, 0),
  ifnull(NULL, 0);

Result:

ifnull(123, 0)  ifnull(NULL, 0)
--------------  ---------------
123             0

The second column was NULL and so 0 was returned instead.

Database Example

This example uses ifnull() in a database query. This demonstrates how ifnull() can be useful when querying data that may contain NULL values.

Take the following table as an example:

ProductId   ProductName    Price     
----------  -------------  ----------
1           Widget Holder  139.5     
2           Widget Stick   89.75     
3           Foo Cap        11.99     
4           Free Widget    0.0       
5           Free Foobar    0.0       
6           Free Beer

Most products have been populated with prices, but the price of Free Beer is NULL.

We can change this to a value of our choosing.

SELECT 
  ProductName,
  ifnull(Price, 0.0)
FROM Products;

Result:

ProductName    ifnull(Price, 0.0)
-------------  ------------------
Widget Holder  139.5             
Widget Stick   89.75             
Foo Cap        11.99             
Free Widget    0.0               
Free Foobar    0.0               
Free Beer      0.0

Now it has the same price as the other free products.

The replacement value doesn’t necessarily need to be a number. You can also replace it with a string.

SELECT 
  ProductName,
  ifnull(Price, 'FREE!')
FROM Products;

Result:

ProductName    ifnull(Price, 'FREE!')
-------------  ----------------------
Widget Holder  139.5                 
Widget Stick   89.75                 
Foo Cap        11.99                 
Free Widget    0.0                   
Free Foobar    0.0                   
Free Beer      FREE!

When Both Arguments are NULL

If both arguments are NULL, then NULL is returned.

SELECT ifnull(NULL, NULL);

Result:

ifnull(NULL, NULL)
------------------