13.4 Working with NULL Values

From time to time, you will find that a cell on a database table is empty. We call this value a NULL value. A NULL value is different than the value zero or even a blank space. A NULL value is an empty value.

In many cases, it is not desirable for a cell to contain a NULL value. When adding a new member to the student organization database, we generally would want to have a value for each of the fields on that table (name, phone number, email address, etc.). There are times, however, when the information for a particular field is unknown. When this happens, we use a NULL value for the field. For example, Randy Woodard has not yet declared a major. To show this, we use a NULL value in the link_to_major field (see Figure 13.7).

It is impossible to use the standard comparison operators (=, <, >, <=, or >=) to detect a NULL value. The syntax 'WHERE link_to_value = NULL' will not work. Instead, we use a different operator that is specific to finding NULL values. The syntax 'IS NULL' is used in the WHERE clause to restrict a query to find NULL values for a field. To return those members who have not yet declared a major (those with a NULL value for the link_to_major field), we would use this syntax in the WHERE clause, 'WHERE link_to_major IS NULL' (see Figure 13.8).

A similar SQL statement will return all of the events that contain a NULL value for the notes field. The syntax for the WHERE clause for this query is WHERE notes IS NULL. The results of this query can be seen in Figure 13.9.

There are also times when we wish to see all of the rows on a table that do not contain a NULL value for a particular field. The SQL statement used to return all rows without a NULL value for a field is IS NOT NULL. Suppose we wish to see all of the events with a non-NULL value for the notes field. The syntax for the WHERE clause in this example is WHERE notes IS NOT NULL. Notice that in Figure 13.10, all of the rows listed in the results for this query contain a value for the notes field.