(mysql.info.gz) Working with NULL
Info Catalog
(mysql.info.gz) Date calculations
(mysql.info.gz) Retrieving data
(mysql.info.gz) Pattern matching
3.3.4.6 Working with `NULL' Values
..................................
The `NULL' value can be surprising until you get used to it.
Conceptually, `NULL' means missing value or unknown value and it is
treated somewhat differently than other values. To test for `NULL',
you cannot use the arithmetic comparison operators such as `=', `<', or
`<>'. To demonstrate this for yourself, try the following query:
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
Clearly you get no meaningful results from these comparisons. Use the
`IS NULL' and `IS NOT NULL' operators instead:
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
Note that in MySQL, `0' or `NULL' means false and anything else means
true. The default truth value from a boolean operation is `1'.
This special treatment of `NULL' is why, in the previous section, it
was necessary to determine which animals are no longer alive using
`death IS NOT NULL' instead of `death <> NULL'.
Two `NULL' values are regarded as equal in a `GROUP BY'.
When doing an `ORDER BY', `NULL' values are presented first if you do
`ORDER BY ... ASC' and last if you do `ORDER BY ... DESC'.
Note that MySQL 4.0.2 to 4.0.10 incorrectly always sorts `NULL' values
first regardless of the sort direction.
Info Catalog
(mysql.info.gz) Date calculations
(mysql.info.gz) Retrieving data
(mysql.info.gz) Pattern matching
automatically generated byinfo2html