Selecting Data from Tables - MySQL - page 115

+---------+------+-------+
| subject | test | score |
+---------+------+-------+
| Jane | A | 47 |
| Jane | B | 50 |
| Jane | C | NULL |
| Jane | D | NULL |
| Marvin | A | 52 |
| Marvin | B | 45 |
| Marvin | C | 53 |
| Marvin | D | NULL |
+---------+------+-------+
You can see that = and <> fail to identify NULL values:
mysql> SELECT * FROM expt WHERE score = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM expt WHERE score <> NULL;
Empty set (0.00 sec)
Write the statements like this instead:
mysql> SELECT * FROM expt WHERE score IS NULL;
+---------+------+-------+
| subject | test | score |
+---------+------+-------+
| Jane | C | NULL |
| Jane | D | NULL |
| Marvin | D | NULL |
+---------+------+-------+
mysql> SELECT * FROM expt WHERE score IS NOT NULL;
+---------+------+-------+
| subject | test | score |
+---------+------+-------+
| Jane | A | 47 |
| Jane | B | 50 |
| Marvin | A | 52 |
| Marvin | B | 45 |
| Marvin | C | 53 |
+---------+------+-------+
The MySQL-specific <=> comparison operator, unlike the = operator, is true even for
two NULL values:
mysql> SELECT NULL = NULL, NULL <=> NULL;
+-------------+---------------+
| NULL = NULL | NULL <=> NULL |
+-------------+---------------+
| NULL | 1 |
+-------------+---------------+
Sometimes it's useful to map NULL values onto some other value that has more meaning
in the context of your application. For example, use IF() to map NULL onto the string
Unknown :