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: w3wiki
Columns: name, rank, courses
Query:
CREATE TABLE w3wiki(
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 w3wiki(id,name,rank,courses)
VALUES('vishu01','Vishu',01,10);
INSERT INTO w3wiki(id,name,rank,courses)
VALUES('sumit02','sumit',02,NULL);
INSERT INTO w3wiki(id,name,rank,courses)
VALUES('neeraj03','Neeraj',NULL,08);
INSERT INTO w3wiki(id,name,rank,courses)
VALUES('aayush04','Aayush',03,NULL);
INSERT INTO w3wiki(id,name,rank,courses)
VALUES('vivek05','Vivek',04,06);
INSERT INTO w3wiki(id,name,rank,courses)
VALUES('harsh06','Harsh',NULL,05);
INSERT INTO w3wiki(id,name,rank,courses)
VALUES('raaj07','Raaj',05,NULL);
--Displaying Data
SELECT * FROM w3wiki;
Output:
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 w3wiki
WHERE courses IS NOT NULL;
Output:
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 w3wiki
WHERE courses IS NOT NULL and rank IS NOT NULL;
Output:
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 w3wiki
WHERE courses IS NOT NULL and rank IS NOT NULL;
Output:
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 w3wiki
SET rank = rank + 1
WHERE rank IS NOT NULL;
Output:
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 w3wiki
WHERE courses IS NOT NULL;
Output:
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.
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.