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, use the
IS NULL and IS
NOT NULL operators, as shown here:
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
You cannot use arithmetic comparison operators such as
=,
<, or
<> to
test for NULL. 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 |
+----------+-----------+----------+----------+
Because the result of any arithmetic comparison with
NULL is also NULL, you
cannot obtain any meaningful results from such comparisons.
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
Section C.5.5.3, “Problems with NULL Values”.
User Comments
The function 'COALESCE' can simplify working with null
values.
for example, to treat null as zero, you can use:
select COALESCE(colname,0) from table where
COALESCE(colname,0) > 1;
in a date field, i used:
ORDER BY
(coalesce(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))
to treat NULL as the current date.
Use IFNULL() in your SELECT statement is make the NULL any value you wish.
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used:
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'
If you want to have NULL values presented last when doing an ORDER BY, try this:
SELECT * FROM my_table ORDER BY ISNULL(field), field [ ASC | DESC ]
Add your own comment.