Kjell-Magne Øierud

Software developer

Missing values in SQL 18 Sep 2008

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

• NULL behaves differently from similar non-value markers in most programming languages.
• Some confuses NULL with the empty string, zero, or false.
• NULLs introduces 3-valued logic. That is, in addition to TRUE and FALSE you also have UNKNOWN, a source of many misunderstandings and errors in SQL statements.
• Many functions treat NULL in a special manner. To remember how NULL is handled by different functions is painful. Some examples: GROUP BY puts all rows with NULLs in the same group, thous treating them equal when in fact the equality is unknown. The SUM aggregate function ignores NULLs when summarizing the values of a column.
• Having columns that might be NULL in a table, typically introduces a penalty on performance.
• Mistakes are Easily made when dealing with ALL, ANY, IN, and EXISTS queries.
• Having many nullable columns in a table might indicate that the table is not properly normalized.

Reasons to use NULL

• It is a good idea to allow NULL when you need to mark a value as missing and there is no easy way handle missing values in any reasonable encoding for the column. Just be aware that you don’t fall in any of the traps mentioned above.

Read more

• Celcko, J. (2005), SQL For Smarties, San Francisco: Morgan Kaufman, Chapter 6
• Celcko, J. (2005), SQL Programming Style, San Francisco: Morgan Kaufman, Chapter 5.3.3
• Garcia-Molina, H., Ullman, J., & Widom, J. (2002), Database systems: The complete book, New Jersey: Prentice Hall, Chapter 6.1.5
• Null (SQL), Wikipedia
• Null (SQL) :: Controversy, Wikipedia
• de Haan, L., Gennick, J. (2005), Nulls: Nothing to Worry About, Oracle Magazine

---