By: Sergey Gigoyan | Updated: 2023-02-09 | Comments | Related: 1 | 2 | More > Snowflake
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:
![1-ISNULL in SQL](/tipimages2/7551_snowflake-null-functions.001.png)
If we try to apply the ISNULL function in Snowflake, it will not work:
SET val=NULL; SELECT ISNULL($val, 0) as VAL;
![2-ISNULL in Snowflake](/tipimages2/7551_snowflake-null-functions.002.png)
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](/tipimages2/7551_snowflake-null-functions.003.png)
![4-Replace ISNULL with IFNULL in Snowflake](/tipimages2/7551_snowflake-null-functions.004.png)
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](/tipimages2/7551_snowflake-null-functions.005.png)
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
![6-example of COALESCE function in SQL](/tipimages2/7551_snowflake-null-functions.006.png)
In Snowflake:
SET val=NULL; SELECT COALESCE($val,10, 20,30) as Val
![7-example of COALESCE function in Snowflake](/tipimages2/7551_snowflake-null-functions.007.png)
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
![8-Replace ISNULL with COALESCE in SQL](/tipimages2/7551_snowflake-null-functions.008.png)
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](/tipimages2/7551_snowflake-null-functions.009.png)
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
![10-NULIFF example in SQL Server](/tipimages2/7551_snowflake-null-functions.010.png)
NULLIF in Snowflake:
SET val1=10; SET val2=10; SELECT NULLIF($val1,$val2) as Val
![11-NULLIF in Snowflake](/tipimages2/7551_snowflake-null-functions.011.png)
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](/tipimages2/7551_snowflake-null-functions.012.png)
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:
- ISNULL (Transact-SQL) - SQL Server | Microsoft Learn
- IFNULL — Snowflake Documentation
- NVL — Snowflake Documentation
- COALESCE (Transact-SQL) - SQL Server | Microsoft Learn
- COALESCE — Snowflake Documentation
- NULLIF (Transact-SQL) - SQL Server | Microsoft Learn
- NULLIF — Snowflake Documentation
Here are other MSSQLTips related articles:
- How to Use SQL Server Coalesce to Work with NULL Values
- Using the SQL ISNULL() Function
- Deciding between COALESCE and ISNULL in SQL Server
- COALESCE SQL Function
About the author
![MSSQLTips author Sergey Gigoyan](/images/Sergey-Gigoyan-2.jpg)
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-09