How to use the SQL IFNULL() function to output al­ter­na­tive values

The SQL IFNULL() function is used to check the value of an ex­pres­sion. If this is NULL, an al­ter­na­tive value is output instead. If it’s not NULL, the system displays its original value.

What is SQL IFNULL()?

In the Struc­tured Query Language, the SQL COALESCE() function is an important tool. However, to return a specific value when an ex­pres­sion is empty or NULL, you can use SQL IFNULL() instead. This function checks the ex­pres­sion and performs one of two actions. If the checked value is NULL, it returns an al­ter­na­tive value that you specify in advance. If the value is not NULL, it returns the original ex­pres­sion. While this may seem confusing in theory, a few practical examples will quickly il­lus­trate the function’s use­ful­ness.

Syntax and function

First, let’s take a look at the basic syntax of SQL IFNULL():

IFNULL(expression, alternative_expression);

The first ex­pres­sion is the one that’s to be checked for its value. The al­ter­na­tive ex­pres­sion is output if the first ex­pres­sion is NULL.

Examples of using the function

To see how SQL IFNULL() works, try these two simple examples. In the first example, the function will recognize that the value of the ex­pres­sion is not NULL. Here is the cor­re­spond­ing code:

IFNULL('This is the first expression', 'This is the alternative');

When you execute the code, the output looks like this:

This is the first expression

The first ex­pres­sion has a value and therefore the function doesn’t have an al­ter­na­tive. The next example is different:

IFNULL(' ', 'This is the alternative');

We get the following result:

This is the alternative

Since the value of the first ex­pres­sion is NULL, the function resorts to the al­ter­na­tive.

Naturally, this also works with numerical values:

IFNULL(10, 15);

We get the following output:

10

Without a stored value, SQL IFNULL() is used:

IFNULL(NULL, 15);

The output is:

15

A practical example

One possible use of SQL IFNULL() could be as follows. We have a table called “De­liv­er­ies”; this has columns for name, delivery address and billing address:

One customer has only entered a delivery address and omitted a billing address. To ensure that all entries are complete, use SQL IFNULL(). The ap­pro­pri­ate code with the SQL command SELECT looks like this:

SELECT Name, IFNULL(billing address, delivery address) Address 
FROM Deliveries;

This gives us a new table in which at least one address is stored for each customer:

Al­ter­na­tives to SQL IFNULL()

In addition to the COALESCE() function mentioned above, there are some other al­ter­na­tives to SQL IFNULL(). SQL NVL() also converts a NULL value to a value of your choice. SQL ISNULL() also checks whether a value is NULL or not and then replaces it with a defined value if required.