How to check and replace NULL values with SQL ISNULL()

SQL ISNULL() is used to check whether an expression is NULL. If it is, the function replaces the NULL value with a substitute value of the same data type.

What is SQL ISNULL()?

In Structured Query Language you can create tables and fill them with values. If you do not assign a value to a field within the table, this field remains empty and is assigned the value NULL, indicating no actual value. Suppose you want to ensure that a substitute value is used instead of NULL. For this purpose, SQL provides the ISNULL() function. This function first checks whether an expression is NULL. If it is, it evaluates a specified substitute expression; otherwise, it considers the original value.

Syntax and function

The functionality of SQL ISNULL() becomes clear when you examine the syntax:

ISNULL(expression, replacement value);

The function first checks if the value of the specified expression is NULL. If it is, the replacement value is output. Otherwise, SQL ISNULL() returns the original expression. It’s important to note that the ‘expression’ and ‘replacement value’ must have the same data type.

Function of SQL ISNULL()

SQL ISNULL() is used within an SQL command such as SELECT. In the following examples, you’ll first see the output if the value of the expression is NULL, followed by the alternative case. Here is the first code example:

SELECT ISNULL(' ', 'This is the substitute value');

The output would be:

This is the substitute value

In the next example, the function is used again, but determines that the expression has a value and therefore outputs the same value:

SELECT ISNULL('This is the actual expression', 'This is the substitute value');

The output is as follows:

This is the actual expression

Practical examples

In the next example, we’ll create a simple table called ‘Orders’. This table contains columns for the customer number, the customer’s name, and an order number:

To structure our table, we can use SQL ISNULL() to replace the value NULL with the placeholder 00000. The corresponding code looks like this:

SELECT customer number, 
ISNULL(order number, 00000), order number 
FROM orders;

By executing the code, we get the following table:

Combination with unit functions

SQL ISNULL() also works in combination with aggregate functions such as SQL AVG(), SQL COUNT() or SUM(). You can see this interaction in the following example. To illustrate, we extend the table from above and add a column for the order value in pounds. The table now looks like this:

If we now want to replace the value NULL with 100 and determine the total order value of all orders, we use the function SUM(). The code is this:

SELECT SUM(ISNULL(order value, 100) 
FROM orders;

The output becomes:

500

The system replaces the value NULL with 100 and thus adds 300 + 100 + 100.

Alternatives for SQL ISNULL()

There are other options that are similar to SQL ISNULL() and are counted among the so-called NULL functions. These include SQL COALESCE(), SQL IFNULL() and SQL NVL(). These also check whether a value is NULL and can then replace it with a substitute value.