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 you are not sure if you are dealing with null values. It returns true if both sides are null or false if both sides are not null:

mysql> SELECT 1 <=> NULL, NULL <=> NULL, 1 <=> 1;
+------------+---------------+---------+
| 1 <=> NULL | NULL <=> NULL | 1 <=> 1 |
+------------+---------------+---------+
|          0 |             1 |       1 |
+------------+---------------+---------+
1 row in set (0.00 sec)

This simple query shows how the null-safe operator works with a variety of inputs.