3.12 Working with NULL Values
3.12.1 Problem
You're trying to
compare column values to NULL, but it
isn't working.
3.12.2 Solution
You have to use the proper comparison operators:
IS NULL,
IS NOT
NULL, or <=>.
3.12.3 Discussion
Conditions involving NULL are special. You cannot
use = NULL or
!= NULL to look for
NULL values in columns. Such comparisons always
fail because it's impossible to tell whether or not
they are true. Even NULL =
NULL fails. (Why? Because you
can't determine whether one unknown value is the
same as another unknown value.)
To look for columns that are or are not NULL, use
IS NULL or
IS NOT NULL.
Suppose a table taxpayer contains taxpayer names
and ID numbers, where a NULL ID indicates that the
value is unknown:
mysql> SELECT * FROM taxpayer;
+---------+--------+
| name | id |
+---------+--------+
| bernina | 198-48 |
| bertha | NULL |
| ben | NULL |
| bill | 475-83 |
+---------+--------+
You can see that = and != do
not work with NULL values as follows:
mysql> SELECT * FROM taxpayer WHERE id = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM taxpayer WHERE id != NULL;
Empty set (0.01 sec)
To find records where the id column is or is not
NULL, the queries should be written like this:
mysql> SELECT * FROM taxpayer WHERE id IS NULL;
+--------+------+
| name | id |
+--------+------+
| bertha | NULL |
| ben | NULL |
+--------+------+
mysql> SELECT * FROM taxpayer WHERE id IS NOT NULL;
+---------+--------+
| name | id |
+---------+--------+
| bernina | 198-48 |
| bill | 475-83 |
+---------+--------+
As of MySQL 3.23, you can also use <=> to
compare values, which (unlike the = operator) is
true even for two NULL values:
mysql> SELECT NULL = NULL, NULL <=> NULL;
+-------------+---------------+
| NULL = NULL | NULL <=> NULL |
+-------------+---------------+
| NULL | 1 |
+-------------+---------------+
3.12.4 See Also
NULL values also behave specially with respect to
sorting and summary operations. See Recipe 6.6 and
Recipe 7.9.
| 
No comments:
Post a Comment