IT'S A BIRD, IT'S A PLANE, IT'S … A NULL!

True to its nature, computers need to be told not only when there is data, but also when there isn't. Humans can readily understand that “zero books” and “no books” refer to the same thing; computers will treat the two as different values. A special marker, NULL, was introduced to address the issue.

Much Ado About Nothing

NULL is a special database concept introduced to represent the absence of value, a void. Despite what some RDBMSs might have implemented, a NULL is neither a zero nor an empty string; it is a special value that can be substituted for an actual value for any data type allowed in the column. NULLs are usually used when the value is unknown or meaningless. A NULL value can later be updated with some real data; it can even become a zero or an empty string, but by itself it is neither.

For example, when you buy a new book, you might not have a few particulars such as cover picture or ISBN number. In such situations, the NULL values are appropriate for these fields.

images SQL standards explicitly state that each data type should include a NULL value that is neither equal to any other value nor is a data type unto its own, but instead stands for an unknown value. NULL has been implemented by all RDBMSs.

Most of the time, NULL behaves according to its nature, hiding away and pretending to be invisible. Once in awhile, it surfaces to ...

Get Discovering SQL: A Hands-On Guide for Beginners now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.