SQL NULL Check in Where clause – IS NULL and IS NOT NULL

In SQL Where clause tutorial, we learned how to use comparison operators such as =, <, > etc in where clause for conditions. However when a column (field) of table has null values then such operators do not work on those columns, in such case we have to use IS NULL & IS NOT NULL operators for the null check. In this guide, we will learn how to deal with null values in SQL.

How to check for null values in SQL

We cannot use comparison operators such as =, <, > etc on null values because the result is undefined. To check for null values we can use IS NULL and IS NOT NULL operators. Lets see the syntax of these operators.

IS NULL Syntax

Null check:

SELECT column_name1, column_name2, column_name3, ...
FROM table_name
WHERE column_nameN IS NULL;

IS NOT NULL Syntax

Not Null check:

SELECT column_name1, column_name2, column_name3, ...
FROM table_name
WHERE column_nameN IS NOT NULL;

SQL IS NULL or empty check Example

Table: Employees

EmployeeName   EmployeeAge  EmployeePhoneNo  EmployeeAddress
------------   -----------  ---------------  ---------------
Cindi           34           95XXXXXXX8        Noida
Linda           35                             Agra
Shaun           33           75XXXXXXX4        Delhi
Timmy           34                             Noida
Pappu           36                             Noida

The following SQL statement will fetch the EmployeeName & EmployeeAddress details of employees where the EmployeePhoneNo column has null value.

SELECT EmployeeName, EmployeeAddress
FROM Employees
WHERE EmployeePhoneNo IS NULL;

Result:

EmployeeName   EmployeeAddress
------------   ---------------
Linda           Agra
Timmy           Noida
Pappu           Noida

SQL IS NOT NULL in where clause Example

Lets take the same example that we have seen above. In this example, we will check for the not null values.
Table: Employees

EmployeeName   EmployeeAge  EmployeePhoneNo  EmployeeAddress
------------   -----------  ---------------  ---------------
Cindi           34           95XXXXXXX8        Noida
Linda           35                             Agra
Shaun           33           75XXXXXXX4        Delhi
Timmy           34                             Noida
Pappu           36                             Noida

The following SQL statement will fetch the EmployeeName & EmployeeAddress details of employees where the value of EmployeePhoneNo column is not null.

SELECT EmployeeName, EmployeeAddress
FROM Employees
WHERE EmployeePhoneNo IS NOT NULL;

Result:

EmployeeName   EmployeeAddress
------------   ---------------
Cindi           Noida
Shaun           Delhi