SQL ISNULL

ANSI Standard SQL provides us with the isnull function to replace all null values with a specific replacement value.

Although the function is defined as part of ANSI SQL, some databases such as PostgreSQL have not adopted it fully. However, you can use the coalesce() function to achieve the same functionality.

Function Syntax

We can express the syntax of the isnull function as shown below:

ISNULL(expression, replacement)

The expression refers to any expression that can be checked for null values.

Replacement refers to the value to be returned if the expression is null.

Keep in mind that the type of the replacement parameter must be convertible to the type of the expression parameter.

This is because the function will perform an implicit cast of the types to that of the expression parameter.

If the expression evaluates to NULL, the function will return the replacement value; otherwise, it returns the expression value.

Example 1

Consider the example below that uses the isnull function with a numeric type:

SELECT

ISNULL(NULL,0);

Since the expression in the above example is NULL, the function will return the replacement value of 0.

You can also perform the same on a non-null value as shown:

SELECT
ISNULL('https://linuxhint.com', 'https://google.com') as url;

Since the expression is not null, the function will return the value of the expression.

Using Isnull in PostgreSQL

You will notice that PostgreSQL does not have the ISNULL function. However, we can use the coalesce() function as a replacement.

The function syntax is as shown:

COALESCE(expression,replacement)

We can run an example as shown:

select coalesce(NULL, 0);

This should return 0 as shown:

The same case applies to a non-null function as shown:

SELECT

coalesce('https://linuxhint.com', 'https://google.com') as url;

The above query should return the value of the expression as shown:

IFNULL – BigQuery

In databases such as BigQuery, the ISNULL function is renamed to IFNULL. The functionality is the same as the ISNULL function.

The syntax is as shown:

IFNULL(expr, replacement);

Example usage:

SELECT IFNULL(NULL, 0) as result

The above should return:

+--------+

| result |

+--------+

| 0 |

+--------+

Conclusion

Using this guide, you understood how to use the isnull function in Standard SQL. You also learned how to use the coalesce function as an alternative to the isnull function.

Thanks for reading!!