IFNull function in the Snowflake

Are you looking to find how to use the IfNull function within the Snowflake cloud data warehouse or maybe you are looking for a solution on how to check the column whether is it null or not in the Snowflake? You may also want to get some specific value in case the column is null. If you are looking for any of these problem solutions then you have landed on the correct page. In this blog post article, I will take you through step by step detailed description of how to use ifnull function. Let’s don’t waste time, and start our step-by-step guide tutorial to understand ifNull function with examples.

IfNull function check the value is null or not. If the value is null it will return the second expression otherwise the same value will be returned.

Syntax of IfNull function:

IFNULL( <expr1> , <expr2> )

Let’s understand the syntax of the IfNull function in detail

How does IfNull function work in Snowflake?

In the IfNull function, it will take two arguments. Both the argument is an expression only.  If the first argument is evaluated as the Null then IfNull() will return the second expression. In case the first expression is not null then IfNull() will return the first expression itself.

For example, ifNull(null, ‘Hello’) will return the result as Hello.

Example of IfNull function in the Snowflake

Let’s create the table 

create or replace table Student (sid int, department_name text, fees int)
    as select * from values
    (1, 'CS', 10000),
    (1, Null, 400 ),
    (2, null, 4500),
    (2, Null, 35000);


+-------------------------------------+
| status                              |
|-------------------------------------|
| Table STUDENT successfully created. |
+-------------------------------------+
1 Row(s) produced. Time Elapsed: 3.187s

select ifnull(department_name, 'No Department') from student;


+------------------------------------------+
| IFNULL(DEPARTMENT_NAME, 'NO DEPARTMENT') |
|------------------------------------------|
| CS                                       |
| No Department                            |
| No Department                            |
| No Department                            |
+------------------------------------------+

Now in the above example, you can see that for the column where the department name is null, IfNull will return the second expression which is ‘NO Department’ in our case.

What is the alternative of the ifnull function in the Snowflake?

 The alternative  for the IfNull function in the Snowflake is the NVL function

NVL also works in a similar fashion as IfNull function. It also has two arguments.  If the first argument is Null it will return the second argument otherwise the first argument will return by the NVL function.

How to use the NVL function in the Snowflake?

NVL function is alias of the Snowflake IfNull function. 

Example of the NVL function in the Snowflake 

select NVL(department_name, 'No Department') from student;


+------------------------------------------+
| NVL(DEPARTMENT_NAME, 'NO DEPARTMENT') |
|------------------------------------------|
| CS                                       |
| No Department                            |
| No Department                            |
| No Department                            |
+------------------------------------------+

Snowflake NVL vs IfNull

Both gives the exact same result in the Snowflake whether you will use the NVL or the IfNull in the Snowflake.

IfNull or NVL Snowflake official Documentation Link

Final Thoughts

By this, we have reached the last section of this article where we have discussed a lot of insightful information about the IfNull function in the Snowflake. In this article, we have also seen what is the different ways in which we can use the IfNull function in the Snowflake. We have also seen the function of the NVL function as well.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

What is IFF function in Snowflake

Pivot Function in Snowflake

• Date_Trunc() in Snowflake
• Date_Add() in Snowflake
• Parse_Json() in Snowflake
• Insert() in Snowflake
• Right() in Snowflake
• StartsWith() in Snowflake
• Replace () in Snowflake
• Position() function
• ListAgg() function
• Like() Function
• LikeAll() Function
• Like Any() Function
• Current_Date() Function
• Row_Number() Function
• Cast() Function
• Show() Function
• ISNULL() Function
• Lag() Function
• ILIKE() Function
• DateDiff() Function
• Substring() Function
• ILIKE ANY() Function
• Round() Function
• To_Date() Function
• Concat() Function
• Length() Function
• TRIM(), LTRIM(), RTRIM()
• SPLIT() Function
• Reverse() Function
• Repeat() Function
• Unicode() Function
• IFNULL() Function
• Pivot() Function
• IFF() Function

Deepak Goyal

Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.