SQL IS NOT NULL Operator

In SQL, the IS NOT NULL Operator is a logical operator. Its application is just the opposite of IS NULL Operator. Unlike IS NULL Operator, this operator returns True, if the value is not a NULL value. It is usually used with a WHERE clause to filter the table data. It filters out the data and separates it from records where specified columns have NULL values. Because of this reasons, it plays a crucial role in data visualization.

In this article, we will learn various applications of IS NOT NULL Operator with their respective explanation and examples. We will be covering all the use cases of IS NOT NULL Operator. We will see how to use this operator wisely in some common scenarios. We will also explore how we can implement this operator with other SQL functions.

SQL IS NOT NULL Operator

SQL IS NOT NULL Operator is a logical Operator. We use this operator to check if a row has a nonempty value in a particular column. In short, we use this operator to detect NOT NULL values. If a check column has a NULL value for a particular row, then this operator will return FALSE. This operator works just opposite of the IS NULL Operator in SQL. This operator generally works WHERE Clause. To summarize this in short, we use the IS NOT NULL Operator to select those rows that have some data present.

Syntax:

SELECT *

FROM table_name

WHERE column_name IS NOT NULL;

Example of IS NOT NULL Operator

Before moving to the example, first, we need to create a table in our database. Let’s create a table in our database

Table Name: geeksforgeeks

Columns: name, rank, courses

Query:

CREATE TABLE geeksforgeeks(
  id varchar(100) PRIMARY KEY,
  name varchar(100),
  rank int,
  courses int
 );

Let’s insert some values in our table and display them.

Query:

INSERT INTO geeksforgeeks(id,name,rank,courses)
VALUES('vishu01','Vishu',01,10);
INSERT INTO geeksforgeeks(id,name,rank,courses)
VALUES('sumit02','sumit',02,NULL);
INSERT INTO geeksforgeeks(id,name,rank,courses)
VALUES('neeraj03','Neeraj',NULL,08);
INSERT INTO geeksforgeeks(id,name,rank,courses)
VALUES('aayush04','Aayush',03,NULL);
INSERT INTO geeksforgeeks(id,name,rank,courses)
VALUES('vivek05','Vivek',04,06);
INSERT INTO geeksforgeeks(id,name,rank,courses)
VALUES('harsh06','Harsh',NULL,05);
INSERT INTO geeksforgeeks(id,name,rank,courses)
VALUES('raaj07','Raaj',05,NULL);

--Displaying Data

SELECT * FROM geeksforgeeks;

Output:

Table – geeksforgeeks

We have created a table and inserted dummy data to the same. Now let’s move to the implementation of IS NOT NULL Operator.

Example 1: IS NOT NULL With SELECT Statement

Let’s display all the records where the courses column holds come value other than a NULL value.

Query:

SELECT * FROM geeksforgeeks
WHERE courses IS NOT NULL;

Output:

Output – 01

We can observe that all the records are displayed where courses hold some integer value other than a NULL value.

NOTE: We applied IS NOT NULL Operator on courses column. Therefore it only filters out NOT NULL values from courses column , not from the other columns, like rank.

Example 2: IS NOT NULL with COUNT()

In this example, we are going to count NOT NULL values from records where a record hold NOT NULL in both of its columns i.e. rank and courses.

Query:

SELECT count(*) as non_empty_records FROM geeksforgeeks
WHERE courses IS NOT NULL and rank IS NOT NULL;

Output:

Output – 2.1

We can clearly spot that there are only two rows where both of these columns i.e. courses and rank hold a NOT NULL value. We can simply display those records too with the below query.

Query:

SELECT * FROM geeksforgeeks
WHERE courses IS NOT NULL and rank IS NOT NULL;

Output:

output – 2.2

Therefore these are the only two records that hold a NOT NULL values in both of the columns i.e. courses and rank.

Example 3: IS NOT NULL with UPDATE Statement

Let’s assume we have to increment the rank of every member by 1. Now in this situation incrementing NULL values by 1 makes no sense. Therefore we have to avoid NULL values. In this example, we will be using the UPDATE Statement too.

Query:

UPDATE geeksforgeeks
SET rank = rank + 1
WHERE rank IS NOT NULL;

Output:

Output – 03

We can spot the difference between the previous table values for rank column and current table value for the rank column. We can see that rank of NOT NULL columns has been incremented by 1.

Example 4: IS NOT NULL with DELETE Statement

Let’s delete all the records from the table where courses column has a NULL value. In this example, we will be using DELETE Statement too.

Query:

DELETE FROM geeksforgeeks
WHERE courses IS NOT NULL;

Output:

Output – 04

From the above image, we can conclude that all the NOT NULL values of courses columns are deleted. Therefore only three records are left where courses column have a NULL value.

Conclusion

IS NOT NULL Operator is a logical operator in SQL. This operator is used to filter out the table data. This means IS NOT NULL Operator is used to separate NULL values and NOT NULL values of the table. This can be found very useful when working with data visualization. In simple words, it works as just opposite of IS NULL Operator. We can filter out NOT NULL values with the help of this operator. In this article, we have seen brief use cases of IS NOT NULL Operator with clear and concise examples with their respective explanation.