IS NOT NULL in SQL

In this post, you’ll learn how to use IS NOT NULL conditional statement in SQL with some examples.

IS NOT NULL in SQL

SQL uses the IS NOT NULL condition to check for a non-NULL value. If a non-NULL value is detected, it returns TRUE; otherwise, it returns FALSE. It is applicable to statements that SELECT, INSERT, UPDATE, or DELETE data.

The suggested comparison operator in SQL to employ for checking for a non-NULL value is IS NOT NULL.

Assume that you have a table “Employee” with the following data.

If you want to return all employees who has department (NON-NULL department id), you can run the below query

SELECT *
FROM Employee
WHERE DepartmentId IS NOT NULL;

You can technically use the IS NOT NUL operator on UPDATE and DELETE statements too as the IS NOT NULL operator is more often used to filter the records.

Using IS NOT NULL with the UPDATE Statement in SQL

For example, if you want to update the Departments of all the Employees with a valid previous departmentids, your query would look similar to this.

UPDATE Employee
SET DepartmentId = 2
WHERE DepartmentId IS NOT NULL;

Using IS NOT NULL with the DELETE Statement in SQL.

Here’s lets look to using the IS NOT NULL condition with the DELETE Statement. In this example, we have an Employee table and we are looking at Deleting all Employees who have a valid DepartmentId filled in.

DELETE Employee
WHERE DepartmentId IS NOT NULL;