How to use Snowflake's IFNULL function

The examples in this post will use the following table, which is called users

What is Snowflake's IFNULL function?

ifnull is a function which returns a defined value if the initial expression is null, otherwise, the initial expression is returned.

The syntax for this function is as follows:

ifnull(expression, default_value)

• expression is the value that will be checked for null, if it isn't null then it's value will be returned
• default_value is the value that will be returned if the initial expression is null

Examples of using IFNULL in Snowflake

ifnull can be used in multiple ways, so let's look at three common use cases.

Using IFNULL with a fixed value

The most basic use case is using a fixed value to replace missing values.

Let's replace the null values in sign_up_source with the value "direct":

select 
user_id, 
ifnull(referral_source,"direct") as referral_source 
from users;

This would return:

Using IFNULL with values from another column

We can also develop the concept further by using values from another column as the default value.

Let's replace the missing values in mean_orders_per_week by using the number_orders and days_since_signup columns.

select 
user_id,
ifnull(
	mean_orders_per_week,
	(number_orders/(days_since_signup/7))
) as mean_orders_per_week
from users;

This would return:

Using IFNULL with a subquery

It's also possible to use a subquery within the ifnull function.

Let's fill in the mean_orders_per_week null values by using the average of the whole column.

select 
d_user_id, 
ifnull(
    mean_orders_per_week,
    (select avg(mean_orders_per_week) from users)
) as mean_orders_per_week
from users;

This would return:

Common questions about IFNULL

ifnull is a simple function used regularly in Snowflake instances, but there are a couple of frequently asked questions about its usage and alternatives.

Should I use IFNULL or ZEROIFNULL?

Snowflake also offers the function zeroifnull, which always returns 0 if the expression is null, otherwise, it returns that initial expression.

The syntax for this function is:

zeroifnull(expression)

This has the exact same result as using ifnull:

ifnull(expression, 0)

So in terms of outcome, there is no difference. However, it is often preferred to use zeroifnull for this specific use case as it is more readable, but the difference is negligible.

Is IFNULL the same as NVL?

nvl is a function from Snowflake which returns a second expression if the first is null, otherwise, the first expression is returned.

It has the following syntax:

nvl(expression_1, expression_2)

ifnull and nvl have the same syntax and behaviour, the only discernible difference is that ifnull has a more descriptive function name and is often preferred among developers because of this.

Summary of how to use IFNULL in Snowflake

To summarise what we have looked at in this article:

Use ifnull to return a fixed value:

select 
ifnull(referral_source,"direct") as referral_source
from users;

Use ifnull to return values derived from other columns:

select 
ifnull(
	mean_orders_per_week,
	(number_orders/(days_since_signup/7))
) as mean_orders_per_week
from users;

Use ifnull to return values derived from a subquery

select 
ifnull(
    mean_orders_per_week,
    (select avg(mean_orders_per_week) from users)
) as mean_orders_per_week
from users;

Related articles

How to use Snowflake's coalesce function
How to use Snowflake's listagg function
How to use Snowflake's concat function
How to select a random sample of rows in Snowflake

References

Snowflake IFNULL documentation
Snowflake ZEROIFNULL documentation
Snowflake NVL documentation