Kjell-Magne Øierud

Software developer

Missing values in SQL 18 Sep 2008

This article is part of a series on selected topics on database management systems

In SQL we use NULL as a placeholder for missing values. It has at least three different uses:

  1. To mark a value in a column as missing.
  2. As placeholders in the result of an outer join where no match are found between rows.
  3. As the result of arithmetic operations with one or more operands that are NULL. Here a NULL means unknown, unknown because information that is needed for evaluating the expression is missing.

If a value in a column is NULL, all you know is that it is missing. If you need to know why, you can store that information in a separate column. Another approach is to disallow NULLs and use an encoding that has distinct values for missing data. An example is the standard encoding for gender: ISO 5218.

The usage of NULL is one of the big controversies in SQL and relational systems. Some feels that it introduces unnecessary complexity, and the general advice seems to be to use NULLs only when there is no better option.

Reasons to avoid NULL

Reasons to use NULL

Read more

Copyright © 2008–2017 Kjell-Magne Øierud.