IS NOT NULL operator is used to compare the value which has not NULL value. It is applicable in CRUD operation like create a query, read query, update query, and delete query.
For SELECT Statement
Syntax:-
SELECT column_name(s)
FROM table_name
WHERE column_name IS NOT NULL
Example:- Suppose we have employees table which has 7 records
+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+-----------+
| 1 | John | Tailor | john@abc.com | USA |
| 2 | Rom | Tailor | rom@abc.com | USA |
| 3 | Andrew | Symonds | andrew@abc.com | Australia |
| 4 | Miacle | Tailor | miacle@abc.com | Australia |
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
| 6 | Virat | Kohli | virat@abc.com | India |
| 7 | rohit | NULL | rohit@abc.com | India |
Find the employees details that have last_name value is not NULL
Query:-
SELECT * FROM employees WHERE last_name IS NOT NULL;
Output:-
+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+-----------+
| 1 | John | Tailor | john@abc.com | USA |
| 2 | Rom | Tailor | rom@abc.com | USA |
| 3 | Andrew | Symonds | andrew@abc.com | Australia |
| 4 | Miacle | Tailor | miacle@abc.com | Australia |
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
| 6 | Virat | Kohli | virat@abc.com | India |
+----+------------+-----------+----------------+-----------+
6 rows in set (0.00 sec)
Note:- In the above example rohit is not showing in the result Output because last_name has a NULL value.