How to Select NULL Values in SQL

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.

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:

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:

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: