SQL Server and Snowflake NULL Functions - ISNULL, COALESCE, NULLIF

Problem

Handling NULLs is a regular part of the database development routine. There are special functions for working with NULLs in both SQL Server and Snowflake. However, when SQL developers switch to Snowflake, they find that many all-familiar SQL NULL-handling functions need to be fixed in Snowflake.

Solution

We will discuss the most common SQL functions for dealing with NULLs and, in contrast, will introduce their counterparts in Snowflake. This article will compare common NULL-handling functions in SQL Server and Snowflake.

ISNULL

For SQL Server developers, the ISNULL function is a well-known function for replacing NULL with the specified value:

DECLARE @val int
 
SELECT ISNULL(@val,0) as Val

The result of the code above will be 0, as the variable is declared but not initialized, so its value is NULL:

If we try to apply the ISNULL function in Snowflake, it will not work:

SET val=NULL;
 
SELECT ISNULL($val, 0) as VAL;

As we can see, we received an error message clearly mentioning that ISNULL is an unknown function. In Snowflake, instead of the ISNULL, the IFNULL function can be used. Let’s replace ISNULL in the previous code with IFNULL:

SET val=NULL;
 
SELECT IFNULL($val, 0) as VAL;

Unlike the previous example, here, when we are starting to type the first letters, we can see that the Snowflake UI suggests the IFNULL function with its description:

3-Snowflake UI suggests the IFNULL function with its description

4-Replace ISNULL with IFNULL in Snowflake

In Snowflake, instead of IFNULL, the NVL function can be used. NVL and IFNULL are aliases:

SET val=NULL;
 
SELECT NVL($val, 0) as VAL;

5-Use NVL instead of IFNULL in Snowflake

COALESCE

SQL Server and Snowflake both support the COALESCE function. This function returns the value of its first non-NULL argument. If all arguments have NULL values, it returns NULL. Unlike ISNULL or IFNULL(NVL), COALESCE can accept more than two arguments. Below is an example of COALESCE function in SQL:

DECLARE @val int
 
SELECT COALESCE(@val,10, 20,30) as Val

In Snowflake:

SET val=NULL;
 
SELECT COALESCE($val,10, 20,30) as Val

It is worth mentioning that the functions discussed in the previous paragraph, ISNULL and IFNULL (NVL), can be replaced by COALESCE. For example, instead of ISNULL in SQL Server, we can use the following:

DECLARE @val int
 
SELECT COALESCE(@val,0) as Val

IFNULL and NVL in the Snowflake code examples above can be rewritten in the following way:

SET val=NULL;
 
SELECT COALESCE($val,0) as Val

9-Replace IFNULL and NVL with COALESCE in Snowflake

NULLIF

NULLIF is a valid function in SQL Server as well as in Snowflake. It accepts two parameters and returns NULL if the values of these two parameters are equal. Otherwise, it returns the value of the first parameter.

NULIFF example in SQL Server:

DECLARE @val1 int=10
DECLARE @val2 int=10
SELECT NULLIF(@val1,@val2) as Val

NULLIF in Snowflake:

SET val1=10;
SET val2=10;
 
SELECT NULLIF($val1,$val2) as Val

It is important to mention that although NULL is not considered equal to NULL, the result of NULLIF with two NULL expressions will be NULL:

SET val1=NULL;
SET val2=NULL;
 
SELECT NULLIF($val1,$val2) as Val

12-result of NULLIF with two NULL expressions will be NULL in Snowflake

The same is true in the case of SQL Server.

Conclusion

Although there are analogs of SQL Server standard NULL-handling functions in Snowflake, their syntaxes do not always match. Therefore, while migrating code from SQL to Snowflake, some NULL-handling functions require special conversion to the corresponding ones in Snowflake.

Next Steps

For additional information, please follow the links below:

Here are other MSSQLTips related articles:

Resources

Community

Subscribe

SQL Server and Snowflake NULL Functions – ISNULL, COALESCE, NULLIF