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.