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

SQL ISNULL() is used to check whether an ex­pres­sion is NULL. If it is, the function replaces the NULL value with a sub­sti­tute value of the same data type.

What is SQL ISNULL()?

In Struc­tured 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, in­dic­at­ing no actual value. Suppose you want to ensure that a sub­sti­tute value is used instead of NULL. For this purpose, SQL provides the ISNULL() function. This function first checks whether an ex­pres­sion is NULL. If it is, it evaluates a specified sub­sti­tute ex­pres­sion; otherwise, it considers the original value.

Syntax and function

The func­tion­al­ity of SQL ISNULL() becomes clear when you examine the syntax:

ISNULL(expression, replacement value);

The function first checks if the value of the specified ex­pres­sion is NULL. If it is, the re­place­ment value is output. Otherwise, SQL ISNULL() returns the original ex­pres­sion. It’s important to note that the ‘ex­pres­sion’ and ‘re­place­ment 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 ex­pres­sion is NULL, followed by the al­tern­at­ive 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 de­term­ines that the ex­pres­sion 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 place­hold­er 00000. The cor­res­pond­ing code looks like this:

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

By executing the code, we get the following table:

Com­bin­a­tion with unit functions

SQL ISNULL() also works in com­bin­a­tion with aggregate functions such as SQL AVG(), SQL COUNT() or SUM(). You can see this in­ter­ac­tion in the following example. To il­lus­trate, 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.

Al­tern­at­ives 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 sub­sti­tute value.