SQL IS NULL Operator

SQL IS NULL operator is a logical operator in SQL used to check the empty values (NULL values) in a column.

For example, suppose we have a table “student”. Our table 3 columns- “id”, “name”, and “rank”. Now if we want to fetch all the data from the table “student” whose row has NULL data in its rank column, in this type of scenario IS NULL operator is used.

In this article, we will discuss the IS NULL Operator and its applications with clear and concise examples.

Syntax

SELECT * FROM table_name

WHERE column_name IS NULL;

Note: A NULL value is different from a Zero Value and Blank Spaces. A field that has NULL value means the field was left blank.

Demo SQL Database

In this tutorial on IS NULL operator, we will use the following table for examples.

user_id	name	problems_solved	coding_score	email
101	Vishu	20	100	example1@gamil.com
102	Sumit	19	99
103	Neeraj	18	98	example2@gamil.com
104	Aayush	17	97
105	Harsh	16		example3@gamil.com
106	Rahul	15		example4@gamil.com
107	Vivek	14	90

To create this table on your system, write the following query:

MySQL

CREATE TABLE geeksforgeeks(
  user_id int PRIMARY KEY,
  name varchar(100),
  problems_solved int,
  coding_score int,
  email varchar(100)
 );
INSERT INTO geeksforgeeks (user_id, name, problems_solved, coding_score, email)
VALUES
    (101, 'Vishu', 20, 100, 'example1@gamil.com'),
    (102, 'Sumit', 19, 99, NULL),
    (103, 'Neeraj', 18, 98, 'example2@gamil.com'),
    (104, 'Aayush', 17, 97, NULL),
    (105, 'Harsh', 16, NULL, 'example3@gamil.com'),
    (106, 'Rahul', 15, NULL, 'example4@gamil.com'),
    (107, 'Vivek', 14, 90, NULL);
SELECT* FROM geeksforgeeks;

IS NULL Operator Examples

Let’s look at som examples of the IS NULL operator in SQL. These examples will teach how to use IS NULL Operator in SQL query and help you practice IS NULL operator.

Example 1: IS NULL with WHERE clause

We use the IS NULL operator with WHERE clause to filter data from the table.

SELECT * FROM geeksforgeeks
WHERE email IS NULL;

Output:

IS NULL with WHERE clause

Example 2 : IS NULL Operator on Multiple Columns

We can use the IS NULL operator on multiple fields using the OR operator.

SELECT * FROM geeksforgeeks
WHERE email IS NULL or coding_score IS NULL;

Output:

IS NULL Operator on Multiple Columns

Example 3: IS NULL with COUNT() Function

Using IS NULL with COUNT() function provides the total number of NULL values in a column.

SELECT count(*) as count_empty_coding_score FROM geeksforgeeks
WHERE coding_score IS NULL;

Output:

IS NULL with COUNT() Function

We can clearly see in our table that we have 2 rows that have NULL values in their coding score column i.e. user_id: 105,106.

Example 4: IS NULL with UPDATE Statement

We can update NULL values with some default values using IS NULL operator with UPDATE statement.

UPDATE geeksforgeeks
SET email = 'default@gmail.com'
WHERE email IS NULL;

Output:

IS NULL with UPDATE Statement

As we can see user_id’s: 102, 104, and 107 previously contain NULL values in their emails column but now they have a default value i.e. “default@gmail.com”.

Example 5: IS NULL with DELETE Statement

We can delete all NULL values with the help of SQL’s DELETE statement.

DELETE FROM geeksforgeeks
WHERE coding_score IS NULL;

Output:

IS NULL with DELETE Statement

We can notice clearly that all those rows deleted which has a null value in their coding score column.

Key Takeaways About IS NULL Operator

• IS NULL operator is used to detect any rows that contain a NULL value in its column.
• IS NULL operator is mostly used with WHERE clause in SQL.
• We can use IS NULL operator on multiple columns using OR operator.
• Using COUNT function we can count total number of NULL values in SQL.
• We can UPDATE or DELETE the NULL values, after filtering them with IS NULL operator.