(mysql.info) problems-with-null
Info Catalog
(mysql.info) using-date
(mysql.info) query-issues
(mysql.info) problems-with-alias
A.5.3 Problems with `NULL' Values
---------------------------------
The concept of the `NULL' value is a common source of confusion for
newcomers to SQL, who often think that `NULL' is the same thing as an
empty string `'''. This is not the case. For example, the following
statements are completely different:
mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
Both statements insert a value into the `phone' column, but the first
inserts a `NULL' value and the second inserts an empty string. The
meaning of the first can be regarded as `phone number is not known' and
the meaning of the second can be regarded as `the person is known to
have no phone, and thus no phone number.'
To help with `NULL' handling, you can use the `IS NULL' and `IS NOT
NULL' operators and the `IFNULL()' function.
In SQL, the `NULL' value is never true in comparison to any other
value, even `NULL'. An expression that contains `NULL' always produces
a `NULL' value unless otherwise indicated in the documentation for the
operators and functions involved in the expression. All columns in the
following example return `NULL':
mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);
If you want to search for column values that are `NULL', you cannot use
an `expr = NULL' test. The following statement returns no rows, because
`expr = NULL' is never true for any expression:
mysql> SELECT * FROM my_table WHERE phone = NULL;
To look for `NULL' values, you must use the `IS NULL' test. The
following statements show how to find the `NULL' phone number and the
empty phone number:
mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = '';
See working-with-null, for additional information and examples.
You can add an index on a column that can have `NULL' values if you are
using the `MyISAM', `InnoDB', or `BDB', or `MEMORY' storage engine.
Otherwise, you must declare an indexed column `NOT NULL', and you
cannot insert `NULL' into the column.
When reading data with `LOAD DATA INFILE', empty or missing columns are
updated with `'''. If you want a `NULL' value in a column, you should
use `\N' in the data file. The literal word ``NULL'' may also be used
under some circumstances. See load-data.
When using `DISTINCT', `GROUP BY', or `ORDER BY', all `NULL' values are
regarded as equal.
When using `ORDER BY', `NULL' values are presented first, or last if
you specify `DESC' to sort in descending order.
Aggregate (summary) functions such as `COUNT()', `MIN()', and `SUM()'
ignore `NULL' values. The exception to this is `COUNT(*)', which
counts rows and not individual column values. For example, the
following statement produces two counts. The first is a count of the
number of rows in the table, and the second is a count of the number of
non-`NULL' values in the `age' column:
mysql> SELECT COUNT(*), COUNT(age) FROM person;
For some data types, MySQL handles `NULL' values specially. If you
insert `NULL' into a `TIMESTAMP' column, the current date and time is
inserted. If you insert `NULL' into an integer column that has the
`AUTO_INCREMENT' attribute, the next number in the sequence is inserted.
Info Catalog
(mysql.info) using-date
(mysql.info) query-issues
(mysql.info) problems-with-alias
automatically generated byinfo2html