Back to cookbooks list Articles Cookbook

How to Select NULL Values in SQL

  • IS NULL
  • IS NOT NULL

Problem:

You want to select rows with the NULL value in a given column.

Example:

Let's see a table named Employees with the following columns: EmployeeID, EmployeeName, Dept, and Salary. Assume the Salary column allows NULL values.

EmployeeIDEmployeeNameDeptSalary
1Jack RusselManager5600
2Jan KowalskiHRnull
3John DoeHR4500
4Mark RusselSales9000
5Jane DoeSalesnull

Now, let’s retrieve all records where the Salary column contains NULL values.

Solution:

To select rows with NULL in a given column, use a special operator IS NULL:

SELECT EmployeeID, EmployeeName, Salary
FROM Employees
WHERE Salary IS NULL;

Here’s the result of the query:

EmployeeIDEmployeeNameSalary
2Jan Kowalskinull
5Jane Doenull

Discussion:

NULL in SQL is used to indicate a missing or unknown value. NULL is special: it is different from all other values, including zero and an empty text field. To test for NULL values specifically in SQL, you must use a special operator IS NULL. It is impossible to test for the NULL value using any of the usual comparison (such as = or <>) operators. A comparison with NULL using a regular comparison operator (=, <, <=, >, >=, or <>) always returns NULL, rather than TRUE or FALSE. For example, this query returns no rows:

SELECT EmployeeID, EmployeeName, Salary
FROM Employees
WHERE Salary = NULL;

While this query:

SELECT EmployeeID, EmployeeName, Salary
FROM Employees
WHERE Salary > 0;

returns only the rows with non-NULL positive salaries. The employees with NULL salaries are omitted in the result:

EmployeeIDEmployeeNameSalary
1Jack Russel5600
3John Doe4500
4Mark Russel9000

SQL also has a similar operator for checking for non-NULL values: IS NOT NULL.

Let’s now do the opposite of the example above and use this operator to select employees whose Salary column contains a non-NULL value.

SELECT EmployeeID, EmployeeName, Salary
FROM Employees
WHERE Salary IS NOT NULL;

This is the result of the query:

EmployeeIDEmployeeNameSalary
1Jack Russel5600
3John Doe4500
4Mark Russel9000

Recommended courses:

Recommended articles:

See also: