NULL Values

Despite the previously discussed rule that a column can have only one data type and logically accept only that type, there is a value that all columns can be defined as, no matter what their data type. This is the value a column is set to when you use the SQL keyword NULL. Essentially, NULL has no data value, so it is not considered a type; it is a system value that indicates to the database that the field it is located within contains no value. The only exception to the rule that any column can contain a NULL is when the NOT NULL constraint is specified for a column.

NULL is often used in places where a value is optional. It can be a convenient way of omitting data without having to resort to strange or arbitrary conventions, such as storing negative values in an integer field to represent omitted data. While your system requirements may change over time, the connotation of NULL is always NULL.

NULL can be thought of as a meta-value: a value that represents a lack of a value , which will never be equivalent to a non-NULL value. One problem often encountered when working with NULL values is that they are easily confused with empty character strings, which return a blank value to the client when selected. The reason this can be confusing is that NULL values also return a blank value when selected; however, they are completely different than empty character strings and this must be understood in order to avoid creating faulty queries or code. A character string column that contains a blank value still contains a string of characters, though the characters that compose the string are blank; thus, there is still a value in the column. A NULL value represents the complete absence of value within the column, not that it is merely blank.

This is an important distinction, as the rules for SQL operations involving the NULL value are quite different than the rules for operations involving empty string values. This internal distinction is especially important in reference to joins , which are discussed in Chapter 4.

The return of both NULL and empty values is shown in Example 3-15, which retrieves a set of five books from the books table. The first SELECT query shows that there appear to be two books which have been inserted without titles. Upon successive querying, however, it becomes clear that while neither have visible titles, one of the books has an empty value for its title (id 100), while the other has a NULL value.

booktown=# 
SELECT id, title FROM books;

  id  |        title
------+---------------------
 7808 | The Shining
  156 | The Tell-Tale Heart
 4513 | Dune
  100 |
  101 |
(5 rows)

booktown=# 
SELECT id, title FROM books WHERE title = '';

 id  | title
-----+-------
 100 |
(1 row)

booktown=# 
SELECT id, title FROM books WHERE title IS NULL;

 id  | title
-----+-------
 101 |
(1 row)

Example 3-16 demonstrates a more practical (and likely) use of NULL in a table called editions, which relates a book's ISBN number to its publication date.

booktown=# 
SELECT isbn, publication FROM editions;

    isbn    | publication
------------+-------------
 039480001X | 1957-03-01
 0394800753 | 1949-03-01
 0385121679 |
(3 rows)

booktown=# 
SELECT isbn, publication FROM editions WHERE publication IS NULL;

    isbn    | publication
------------+-------------
 0385121679 |
(1 row)

NULL might be used in this manner in order to represent books with editions that are not yet published, or for books whose publication date was unknown when entered into the database. It could be misleading to supply some arbitrarily illogical date for a book fitting either of these criteria, and in both cases, NULL makes sense as a solution.