How to use the SQL IFNULL() function to output alternative values

The SQL IFNULL() function is used to check the value of an expression. If this is NULL, an alternative value is output instead. If it’s not NULL, the system displays its original value.

What is SQL IFNULL()?

In the Structured Query Language, the SQL COALESCE() function is an important tool. However, to return a specific value when an expression is empty or NULL, you can use SQL IFNULL() instead. This function checks the expression and performs one of two actions. If the checked value is NULL, it returns an alternative value that you specify in advance. If the value is not NULL, it returns the original expression. While this may seem confusing in theory, a few practical examples will quickly illustrate the function’s usefulness.

Syntax and function

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

IFNULL(expression, alternative_expression);

The first expression is the one that’s to be checked for its value. The alternative expression is output if the first expression 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 expression is not NULL. Here is the corresponding 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 expression has a value and therefore the function doesn’t have an alternative. 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 expression is NULL, the function resorts to the alternative.

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 “Deliveries”; 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 appropriate 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:

Alternatives to SQL IFNULL()

In addition to the COALESCE() function mentioned above, there are some other alternatives 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.