GNU Info

(mysql.info)Working with NULL

---

Next: Pattern matching Prev: Date calculations Up: Retrieving data

---

Enter node , (file) or (file)node

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 |
     +-----------+---------------+

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'.