NULL handling is a common task in database development. Both SQL Server and Snowflake provide special functions for working with NULLs. However, when transitioning from SQL Server to Snowflake, developers often encounter differences in the NULL-handling functions. In this article, we will compare the most common NULL-handling functions in SQL Server and Snowflake.

ISNULL

In SQL Server, the ISNULL function is commonly used to replace NULL values with a specified value. For example:

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

The result of the above code will be 0, as the variable is declared but not initialized, resulting in a NULL value. However, when attempting to use the ISNULL function in Snowflake, it will not work:

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

In Snowflake, the IFNULL function can be used instead of ISNULL. Let’s replace ISNULL in the previous code with IFNULL:

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

Unlike the previous example, when typing the first letters, the Snowflake UI suggests the IFNULL function with its description. In Snowflake, the NVL function can also be used instead of IFNULL. NVL and IFNULL are aliases:

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

COALESCE

Both SQL Server and Snowflake 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. Here’s an example of the COALESCE function in SQL Server:

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

In Snowflake, the COALESCE function can be used in a similar way:

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

It’s worth mentioning that the functions discussed earlier, 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

Similarly, IFNULL and NVL in Snowflake can be rewritten as:

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

NULLIF

NULLIF is a valid function in both SQL Server and 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. Here’s an example of NULLIF in SQL Server:

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

In Snowflake, NULLIF can be used in a similar way:

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

It’s important to note 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

The same behavior applies in SQL Server.

Conclusion

While Snowflake provides analogs of the standard NULL-handling functions in SQL Server, their syntaxes do not always match. When migrating code from SQL Server to Snowflake, it’s important to be aware of these differences and make the necessary conversions to the corresponding functions in Snowflake. By understanding the similarities and distinctions between SQL Server and Snowflake’s NULL-handling functions, developers can effectively handle NULL values in their database applications.

Article Last Updated: 2023-02-09