Test whether the value of an SQL expression is NULL

Objective

To test whether the value of an SQL expression is NULL

Background

NULL behaves differently from other values in SQL because it is not equal to itself. For this reason if you attempt to select null values using the equals operator then the result is the empty set.

Scenario

Suppose that you have an SQL table named employees with the following schema:

CREATE TABLE employees (
  employee_number INTEGER PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  dob DATE);

You wish to obtain a list of employee records which do not specify a date of birth, as indicated by the dob field having a value of NULL.

Method

The predicate IS NULL can be used to determine whether an SQL expression has a null value:

SELECT * FROM employees WHERE dob IS NULL;

Variations

Testing whether a value is non-null

One way to test for non-null values is to use the IS NOT NULL predicate:

SELECT * FROM employees WHERE dob IS NOT NULL;

Another is to take the result of IS NULL and invert it using NOT:

SELECT * FROM employees WHERE NOT dob IS NULL;

When applied to a scalar value (as in this instance) the result is the same either way. When applied to a row:

• IS NOT NULL should return true only when all components are non-null, whereas
• NOT ... IS NULL should do so if any component is non-null.

Note, however, that:

• Prior to PostgreSQL version 8.2, IS NOT NULL behaved incorrectly when applied to a row-valued argument.
• None of the tested versions of MySQL allow row-valued arguments to either IS NULL or IS NOT NULL.

• Conventions
• Privacy
• Legal