Managing & Using MySQL, 2nd Edition

Null’s Idiosyncrasies

Null is a tricky concept for most people new to databases to understand. As in other programming languages, null is not a value, but the absence of a value. This concept is useful, for example, if you have a customer profiling database that gradually gathers information about your customers as they offer it. When you first create a record, for example, you may not know how many pets the customer has. You want that column to hold NULL instead of 0 so you can tell the difference between customers with no pets and customers whose pet ownership is unknown.

The concept of null gets a little funny when you use it in SQL calculations. Many programming languages use null as simply another kind of value. In Java, the following syntax evaluates to true when the variable is null and false when it is not:

str == null

The similar expression in SQL, COL = NULL, is neither true nor false—it is always NULL, no matter what the value of the COL column. The following query will therefore not act as you might expect:

SELECT title FROM book WHERE author = NULL;

Because the WHERE clause will never evaluate to true no matter what value is in the database for the author column, this query always provides an empty result set—even when you have author columns with NULL values. To test for “nullness,” use the IS NULL and IS NOT NULL operators:

SELECT TITLE FROM BOOK WHERE AUTHOR IS NULL;

MySQL also provides a special operator called the null-safe operator <=>, which you can use when ...