(mysql.info) working-with-null

Info Catalog

(mysql.info) date-calculations

(mysql.info) retrieving-data

(mysql.info) 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 `a missing unknown value' and it is treated
 somewhat differently from 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'.
 
 A common error when working with `NULL' is to assume that it is not
 possible to insert a zero or an empty string into a column defined as
 `NOT NULL', but this is not the case. These are in fact values, whereas
 `NULL' means `not having a value.' You can test this easily enough by
 using `IS '[`NOT']` NULL' as shown:
 
      mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
      +-----------+---------------+------------+----------------+
      | 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
      +-----------+---------------+------------+----------------+
      |         0 |             1 |          0 |              1 |
      +-----------+---------------+------------+----------------+
 
 Thus it is entirely possible to insert a zero or empty string into a
 `NOT NULL' column, as these are in fact `NOT NULL'. See 
 problems-with-null.
 

Info Catalog

(mysql.info) date-calculations

(mysql.info) retrieving-data

(mysql.info) pattern-matching