Snowflake NULL Handling Functions, Usage and Examples

A NULL value in a relational database is a special marker used in SQL to indicate that a data value is UNKNOWN or does not exist in the database. In other words, a NULL value is just a placeholder to denote values that are missing or it is unknown. Snowflake supports NULL handling functions that are available in other cloud data warehouse such as Redshift, Azure Synapse, etc. Along with those, Snowflake also supports many extended functions to handle null values. In this article, we will check Snowflake NULL handling functions, usage and some examples.

Snowflake NULL Handling Functions

You can use NULL functions to handle NULL values that may come as a part of your data. For example, you can use NULL functions to display zero in place of NULL on reports. Similarly, you can use a NULL function to handle divided by zero error in case of mathematical calculation.

Following is the list of Snowflake NULL handling functions.

Null Handling Function	Description
NVL	Returns parameter2 if parameter1 is null. Otherwise, parameter1 is returned.
NVL2	If parameter1 is NOT NULL then NVL2 returns parameter2. If parameter1 is NULL then NVL2 returns parameter3.
COALESCE	Returns first non-null value
IFNULL	IFNULL is `NVL` alias.
ZEROIFNULL	Returns zeros if the value is NULL.
NULLIF	Returns NULL if parameters are same. Otherwise, parameter1 is returned.
IS NULL	Determines whether an expression is NULL.
IS NOT NULL	Determines whether an expression is not NULL.
EQUAL_NULL	Compares whether two expressions are equal.
IS_NULL_VALUE JSON	Returns true if its VARIANT argument is a JSON null value.

Now let us check these Snowflake NULL functions briefly

Snowflake NVL and NVL2 Functions

Snowflake NVL takes two parameters. If parameter1 is NULL then parameter2 is returned. Otherwise, parameter1 is returned.

For examples,

SELECT NVL('value1', NULL) as NVL_test;
+----------+
| NVL_TEST |
|----------|
| value1   |
+----------+

SELECT NVL(NULL, 'value1') as NVL_test;
+----------+
| NVL_TEST |
|----------|
| value1   |
+----------+

Similarly, Snowflake NVL2 function takes three parameters. If parameter1 is NOT NULL then NVL2 returns parameter2. If parameter1 is NULL then NVL2 returns parameter3.

For example,

SELECT NVL2('value1', 'value2', NULL) as NVL2_test;
+-----------+
| NVL2_TEST |
|-----------|
| value2    |
+-----------+

SELECT NVL2(NULL, 'value2', 'value3') as NVL2_test;
+-----------+
| NVL2_TEST |
|-----------|
| value3    |
+-----------+

COALESCE Function in Snowflake

Snowflake COALESCE function returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.

For example,

SELECT COALESCE(NULL, 'value2', 'value3') as coalesce_test;
+----------------+
| CLOALESCE_TEST |
|----------------|
| value2         |
+----------------+

IFNULL Function in Snowflake

Snowflake IFNULL is NVL alias. It takes two parameters. If parameter1 is NULL then IFNULL returns parameter2. Otherwise, parameter1 is returned.

For example,

SELECT IFNULL('value1', NULL) as IFNULL_test;
+-------------+
| IFNULL_TEST |
|-------------|
| value1      |
+-------------+

SELECT IFNULL(NULL, 'value1') as IFNULL_test;
+-------------+
| IFNULL_TEST |
|-------------|
| value1      |
+-------------+

ZEROIFNULL Function in Snowflake

The Snowflake ZEROIFNULL function returns 0 if its argument is null; otherwise, returns its argument. This function is mainly used in reporting queries where zero is displayed instead of NULL values.

For example,

SELECT ZEROIFNULL(NULL) as ZEROIFNULL_test;
+-----------------+
| ZEROIFNULL_TEST |
|-----------------|
|               0 |
+-----------------+

SELECT ZEROIFNULL(123) as ZEROIFNULL_test;
+-----------------+
| ZEROIFNULL_TEST |
|-----------------|
|             123 |
+-----------------+

NULLIF Function in Snowflake

Snowflake NULLIF function returns NULL if parameter1 and parameter2 are equal. Otherwise, returns parameter1.

For example,

SELECT NULLIF('value2', 'value2') as nullif_test;
+-------------+
| NULLIF_TEST |
|-------------|
| NULL        |
+-------------+

SELECT NULLIF('value2', 'value3') as nullif_test;
+-------------+
| NULLIF_TEST |
|-------------|
| value2      |
+-------------+

IS NULL and IS NOT NULL Operator in Snowflake

Snowflake IS NULL and IS NOT NULL determines whether an expression is NULL or is not NULL. These operators are mainly used in WHERE clause and CASE statement. These are the common operators to filter out NULL values.

For examples,

SELECT id,
       CASE
              WHEN dept IS NULL THEN 0
              ELSE dept
       END AS dept,
       band
FROM   Values
	   (1, 0, 5),
       (2, 0, NULL),
       (3, NULL, 5),
       (4, NULL, NULL) AS t(id, dept, band)
WHERE  id IS NOT NULL;

+----+------+------+
| ID | DEPT | BAND |
|----+------+------|
|  1 |    0 |    5 |
|  2 |    0 | NULL |
|  3 |    0 |    5 |
|  4 |    0 | NULL |
+----+------+------+

EQUAL_NULL Function in Snowflake

Snowflake EQUAL_NULL compares whether two expressions are equal. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality.

For example,

SELECT EQUAL_NULL(dept, band) as null_check
FROM   Values
       (1, 0, 5),
       (2, 0, NULL),
       (3, NULL, 5),
       (4, NULL, NULL) AS t(id, dept, band);

+------------+
| NULL_CHECK |
|------------|
| False      |
| False      |
| False      |
| True       |
+------------+

IS_NULL_VALUE JSON Function in Snowflake

Snowflake IS_NULL_VALUE returns true if its VARIANT argument is a JSON null value. JSON null values is different from SQL NULL values.

For example,

select v, v:a, is_null_value(v:a)
    from
        (select parse_json(column1) as v
         from values
             ('{"a": null}'),
             ('{"a": "foo"}'),
             (null)
        );

+--------------+-------+--------------------+
| V            | V:A   | IS_NULL_VALUE(V:A) |
|--------------+-------+--------------------|
| {            | null  | True               |
|   "a": null  |       |                    |
| }            |       |                    |
| {            | "foo" | False              |
|   "a": "foo" |       |                    |
| }            |       |                    |
| NULL         | NULL  | NULL               |
+--------------+-------+--------------------+