SQL IS NOT NULL Operator

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.