Snowflake NULL Functions - NVL2, ZEROIFNULL, EQUAL_NULL, IS_NULL

Problem

In the previous article, we discussed Snowflake equivalents of SQL Server NULL-handling functions. In this article, we will cover some Snowflake NULL-handling functions that do not exist in SQL Server.

Solution

We will illustrate some Snowflake functions that deal with NULLs that are missing in SQL Server. We will also discuss how the corresponding logic can be implemented in SQL. Let's go!

NVL2

SQL Server does not support the NVL2 function (it is worth mentioning that ORACLE supports it). This function accepts three parameters and returns the value of the second parameter if the first parameter is not NULL. Otherwise, it returns the value of the third parameter. It extends the functionality of the NVL function discussed in the previous article. Let's see how it works. In the example below, we can see that NULL is provided as the first parameter:

SET val=NULL;
 
SELECT NVL2($val, 10*$val, 0) as VAL;

Therefore, the value of the last (third) parameter is returned, which is 0:

If we provide some non-NULL value, we can see that the second parameter is chosen as a return value:

SET val=10;
 
SELECT NVL2($val, 10*$val, 0) as VAL;

So, according to the logic of the second parameter, 10 is multiplied by 10, and 100 is returned as the result:

The logic of this function can be written in SQL in different ways. Below, are two examples to implement the same logic as above:

DECLARE @val INT=10
 
--1
SELECT CASE WHEN @val IS NULL THEN 0
            ELSE @val*10
      END AS Val
 
--2
SELECT IIF( @val IS NOT NULL, @val*10 , 0 ) AS Val

ZEROIFNULL

The ZEROIFNULL function accepts only one parameter and, as its name suggests, returns 0 if the parameter is null. Otherwise, it returns the value of the parameter:

SET val=NULL;
 
SELECT ZEROIFNULL($val) as VAL;

SET val=10;
 
SELECT ZEROIFNULL($val) as VAL;

Note: The value of the input parameter should be a numeric value (or NULL). In the example below, a string value that cannot be converted to a numeric value is provided, and an error message is returned:

SET val='ABC';
 
SELECT ZEROIFNULL($val) as VAL;

SQL Server does not support the ZEROIFNULL function. However, its functionality can be easily replaced in several ways. For example:

DECLARE @val INT=10
 
--1
SELECT CASE WHEN @val IS NULL THEN 0
            ELSE @val
      END AS Val
 
--2
 
SELECT IIF( @val IS NOT NULL, @val, 0 ) AS Val

EQUAL_NULL

The EQUAL_NULL function is also one of the Snowflake functions missing in SQL Server. This function compares two arguments and returns TRUE if they are equal and FALSE if not. Unlike regular equality operations, EQUAL_NULL is NULL-safe which means it considers NULLs as equal. Therefore, if both parameters are NULL, it returns TRUE.

SET val1=10;
SET val2=20;
 
SELECT equal_null($val1, $val2)as VAL;

SET val1=10;
SET val2=10;
 
SELECT equal_null($val1, $val2)as VAL;

SET val1=NULL;
SET val2=10;
 
SELECT equal_null($val1, $val2)as VAL;

SET val1=NULL;
SET val2=NULL;

SELECT equal_null($val1, $val2)as VAL;

As we can see, in the first and third examples, the parameters are not equal, so the result is FALSE. In the second example, we passed equal values and received TRUE. In the last example, we passed two NULL values and still received TRUE because, as mentioned above, this function treats NULLs as equal. In SQL Server, we can implement the logic of EQUAL_NULL in the following way, for instance:

DECLARE @val1 INT=10
DECLARE @val2 INT=20
DECLARE @Val BIT=0
 
--1
SELECT @val=CASE WHEN ISNULL(@val1,0)=ISNULL(@val2,0) THEN 1
            ELSE 0
      END
 
SELECT @val as Val

IS_NULL_VALUE

IS_NULL_VALUE is another Snowflake-specific function that does not exist in SQL. In semi-structured data, Snowflake supports two types of NULL values:

SQL NULL – the value is missing or unknown
VARIANT or JSON NULL – To distinguish JSON null values from SQL NULLs in the VARIANT string, they are stored as a "null" string.

The IS_NULL_VALUE function accepts a variant argument and returns TRUE if the argument is a JSON NULL. Let's run the code below and see the results:

SELECT val, val:A, IS_NULL_VALUE(val:A), IFNULL(val:A, 0)
    FROM
        (SELECT PARSE_JSON(column1) as val
         FROM VALUES
             (null),
             ('{"A": null}'),
             ('{"A": "ABC"}')
 
        );

The value in the first row is SQL NULL, so IFNULL returns 0, and IS_NULL_VALUE returns NULL. In the second row, the value is JSON NULL. Therefore, IS_NULL_VALUE returns TRUE, and IFNULL returns "null" as a string because, for this function, it is just a string and not a NULL value. In the third row, IS_NULL_VALUE returns FALSE as its parameter is a string and not a JSON NULL. IFNULL returns that string:

Conclusion

As we can see, SQL Developers, who switch to working with Snowflake can find some NULL-handling functions that SQL Server does not provide. However, the logic provided with these functions can be easily converted to SQL code in different ways as shown in the examples.

Next Steps

Check out this related article: Working with SQL Server and Snowflake NULL Functions - ISNULL, COALESCE, NULLIF

For more information, check out the following Snowflake documentation:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-02-23

Snowflake for SQL Server Developers - NULL Functions - Part 2