IS NULL() function in Snowflake?

Explained: IS NULL() function in Snowflake?

by

Are you looking to find how to use the IS NULL() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the IS NULL function in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what and how to use IS NULL() function. I will explain the IS NULL() function by taking a practical example. So, don’t waste time let’s start a step-by-step guide to understanding what is the IS NULL() expression.

What is IS NULL() Function?

IS NULL function helps to compare the values that are NULL. For example, if in a table of 50 rows, if we want to get the rows based on whether a specific column is either null or not then you can use IS NULLfunction.

How can we use IS NULL() Function in Snowflake?

IS NULL() function is used to determine whether an expression is NULL or not NULL. If we want to get NULL values or not NULL values are completed depending on your requirement.

Syntax for IS NULL function in Snowflake

-- Syntax : 

Expression IS [ NOT ] NULL

Returns:

Returns Boolean true or false.

Examples :

Create a table and Insert the data.

Firstly let’s create a table and insert some sample data.

create or replace TABLE EMP (
ID NUMBER(38,0),                                                               
NAME VARCHAR(30) COMMENT 'NAME OF THE EMPLOYEE',                                                               
INFO VARCHAR(20) DEFAULT 'NOT-NULL',                                                               
AMOUNT NUMBER(38,0)                                                               
);


select * from EMP;
+----+-------------+----------+--------+
| ID | NAME        | INFO     | AMOUNT |
|----+-------------+----------+--------|
|  1 | Ram         | NOT-NULL |   2000 |
|  2 | RAJU        | NOT-NULL |   2500 |
|  3 | Sameer      | NOT-NULL |   1500 |
|  4 | Sai Kumar   | NOT-NULL |    250 |
|  5 | Naveen Sai  | NOT-NULL |   1000 |
|  6 | Seshi Kumar | NOT-NULL |  13500 |
|  7 | Rajiv Ram   | NOT-NULL |  18000 |
|  8 | NULL        | NOT-NULL |   NULL |
+----+-------------+----------+--------+

Apply IS NULL and IS NOT NULL to the table data.

//USE - IS NOT NULL

select * from EMP where name is not null;

+----+-------------+----------+--------+
| ID | NAME        | INFO     | AMOUNT |
|----+-------------+----------+--------|
|  1 | Ram         | NOT-NULL |   2000 |
|  2 | RAJU        | NOT-NULL |   2500 |
|  3 | Sameer      | NOT-NULL |   1500 |
|  4 | Sai Kumar   | NOT-NULL |    250 |
|  5 | Naveen Sai  | NOT-NULL |   1000 |
|  6 | Seshi Kumar | NOT-NULL |  13500 |
|  7 | Rajiv Ram   | NOT-NULL |  18000 |
+----+-------------+----------+--------+

//USE - IS NULL

select * from EMP where name is null;

+----+------+----------+--------+
| ID | NAME | INFO     | AMOUNT |
|----+------+----------+--------|
|  8 | NULL | NOT-NULL |   NULL |
+----+------+----------+--------+

Full Example of IS NULL function in Snowflake.

-- Create a table

create or replace TABLE EMP1 (
	ID NUMBER(38,0),
	NAME VARCHAR(30),
	INFO VARCHAR(20),
	AMOUNT NUMBER(38,0)
);

-- Insert Data

insert into emp(id,name,amount) values 
(1,'Ram',2000),
(2,'RAJU',2500),
(3,'Sameer',1500),
(4,'Sai Kumar',250),
(5,'Naveen Sai',1000),
(6,'Seshi Kumar',13500),
(7,'Rajiv Ram',18000),
(8,NULL,NULL)

-- Apply IS NULL() function to the table data

select * from emp1 where amount is NULL;
+----+------+----------+--------+
| ID | NAME | INFO     | AMOUNT |
|----+------+----------+--------|
|  8 | NULL | NOT-NULL |   NULL |
+----+------+----------+--------+

-- Apply IS NOT NULL() function to the table data

select * from emp1 where amount is not NULL;
+----+-------------+----------+--------+
| ID | NAME        | INFO     | AMOUNT |
|----+-------------+----------+--------|
|  1 | Ram         | NOT-NULL |   2000 |
|  2 | RAJU        | NOT-NULL |   2500 |
|  3 | Sameer      | NOT-NULL |   1500 |
|  4 | Sai Kumar   | NOT-NULL |    250 |
|  5 | Naveen Sai  | NOT-NULL |   1000 |
|  6 | Seshi Kumar | NOT-NULL |  13500 |
|  7 | Rajiv Ram   | NOT-NULL |  18000 |
+----+-------------+----------+--------+

When you should use IS NULL Function in Snowflake?

There are certain use case scenarios when it is recommended to use the IS NULL function within the Snowflake cloud data warehouse which are as follows:

  • If we want to get the data that is null then in that case we can make use of IS NULL function.
  • If we want to get the data that is not null then in that case we can make use of IS NOT NULL function where NOT in IS NULL is optional.

Real World Use Case Scenarios for IS NULL Function in Snowflake

  • If we want to know the customer’s data which is not filled or empty ( IS NULL ).
  • If we want to know the employee’s data which is filled or empty ( IS NOT NULL ).

IS NULL Snowflake Official Documentation Link

An empty string is null in Snowflake?

An empty string in Snowflake is not equal to NULL. So, the return value (IS NULL) will be false.

Final Thoughts

In this article, we have learned about IS NULL() function and uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

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

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Revanth Kumar