AP 16 - Beware of NULL in combination with not equal operator (!=, <>)

Severity

Type

Problem

When filtering with the Not Equal operator (!=, <>) rows with NULL values in the filtered column are not returned.

Let’s go through an example.

Table with sample data

The following query only returns the row with customer_id 3. It does not return the row with cutsomer_id 1. For this row the age is unknown (NULL) and we don’t know what the age is. It may be 18 or it may not be 18.

SELECT customer_id
  FROM customer
 WHERE age<>18

SELECT car_name 
  FROM car 
 WHERE car_type NOT IN('Coupe')

The query will skip the cars which don’t have their type defined, irrespectively whether that was intentional or not.

Solution

However, if the intention is to also return records where we know the customer age is not 18 (so not 18 or where we don’t know the age of the customer) then we need to rewrite our query:

SELECT customer_id
  FROM customer
 WHERE age<>18
    OR age IS NULL

The ANSI SQL 99 standard also introduced the IS DISTINCT FROM function. However, it is not supported by all database vendors

SELECT customer_id
  FROM customer
 WHERE age IS DISTINCT FROM 18;

SELECT car_name 
  FROM car 
 WHERE car_type NOT IN('Coupe') 
   AND car_type is NOT NULL

Legitimate use of anti pattern

The anti pattern is legitimate if we want to exclude records where the age is unknown (NULL) from our result set.

If the column has a NOT NULL constraint then you can possibly ignore the anti pattern. However, in the spirit of a defensive programming style it is good practice to cater for NULLs. The business rule might change and the NOT NULL constraint dropped in the future.