The SQL keyword NULL is a very special exception to the rules that we have introduced regarding columns and discrete data types. While a column may only have one data type, any column (excluding those explicitly defined as NOT NULL ) may contain the NULL value.

NULL can be thought of as a meta-value: a value which represents a lack of a value , which will never be equivalent to a non- NULL value. In practice, this can cause some confusion if the nature of NULL is not fully understood. This is demonstrated in Example 3-16 .

Example 3-16. Observing NULL Values

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 shows a set of five books in 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.

This is an important distinction, as the rules for SQL statements upon the NULL value are quite different from those applied to empty values. This is an especially important point in reference to joins , which are discussed in Chapter 4 .

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 .

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

Example 3-17. Using NULL Values

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 which 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.