How to Handle NULL in Snowflake? Functions

Snowflake is one of the leading cloud databases that you can use to create cloud data warehouse. It is built for a cloud and supports almost all features that are available in the current on-premises or cloud databases. In my other article, we have seen why you should learn Snowflake database. In this article, we will check how to handle NULL values in a Snowflake cloud database. We will also check null handling functions available in Snowflake.

How to Handle NULL in Snowflake?

A NULL value in any 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 specify values that are missing or it is unknown. You can compare NULL value as a NaN values from Python. Snowflake supports NULL handling functions that are available in other on-premises such as Oracle, Teradata, etc. and cloud data warehouse such as Redshift, Azure Synapse, etc. Along with those, Snowflake also supports many extended functions to handle null values.

Snowflake NULL Handling Functions

Snowflake supports many NULL handling functions, you can use those functions to handle NULL values your data. For example, you can use NULL functions to replace a NULL with zero on reports. Similarly, you can use a NULL function to handle divided by zero error in case of complex mathematical calculations. Following is the list of Snowflake NULL handling functions:

Snowflake NULL Functions	Description
NVL	The NVL function returns parameter2 if parameter1 is null. Otherwise, parameter1 is returned.
NVL2	If parameter1 is NOT NULL then NVL2 function returns parameter2. If parameter1 is NULL then NVL2 function returns parameter3.
COALESCE	The COALESCE function returns first non-null value
IFNULL	IFNULL is `NVL` alias. It returns parameter2 if parameter1 is null. Otherwise, parameter1 is returned.
ZEROIFNULL	The ZEROIFNULL function returns zeros if the input value is NULL.
NULLIF	The NULLIF function returns NULL if input 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 null handling function takes two parameters. If parameter1 is NULL, then parameter2 is returned. Otherwise, parameter1 is returned.

For examples,

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

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

Similarly, Snowflake NVL2 null handling 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 null handling function returns the first non-NULL expression among its arguments,. The function will return 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 null function returns 0 if its argument is null; otherwise, returns its argument. These types of functions are useful when generating report where you need to replace NULL with a readable value.

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.

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               |
+--------------+-------+--------------------+