AP 10 - Beware of filtering for NULL

Severity

Type

Problem

Many software developers are caught off-guard by the behaviour of NULL in SQL. Unlike in most programming languages, SQL treats NULL as a special value, different from zero, false, or an empty string.

In SQL, a comparison between a NULL value and any other value (including another NULL) using a comparison operator, e.g. =, !=, < will result in a NULL, which is considered as false for the purposes of a where clause (strictly speaking, it’s “not true”, rather than “false”, but the effect is the same).

The reasoning is that a NULL means “unknown”, so the result of any comparison to a NULL is also “unknown”. So you’ll get no hit on rows by coding where my_column = NULL.

Incorrect usage of NULL with comparison operato

SELECT a
      ,b
      ,c
  FROM table_1
 WHERE a=NULL

Solution

SQL provides the special syntax for testing if a column is NULL, via IS NULL and IS NOT NULL, which is a special condition to test for a NULL (or not a NULL).

Correct usage of NULL with IS NULL operator

SELECT a
      ,b
      ,c
  FROM table_1
 WHERE a IS NULL

Legitimate use of the anti pattern

None