6.6 Sorting and NULL Values
6.6.1 Problem
You want to sort a column that may
contain NULL values.
6.6.2 Solution
The placement of NULL values in a sorted list has
changed over time and depends on your version of MySQL. If
NULL values don't come out in the
desired position within the sort order, trick them into appearing
where you want.
6.6.3 Discussion
When a sorted column contains NULL values, MySQL
puts them all together in the sort order. It may seem a bit odd that
NULL values are grouped this way, given that (as
the following query shows) they are not considered equal in
comparisons:
mysql> SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
| NULL |
+-------------+
On the other hand, NULL values conceptually do
seem more similar to each other than to non-NULL
values, and there's no good way to distinguish one
NULL from another, anyway. However, although
NULL values group together, they may be placed at
the beginning or end of the sort order, depending on your version of
MySQL. Prior to MySQL 4.0.2, NULL values sort to
the beginning of the order (or at the end, if you specify
DESC). From 4.0.2 on, MySQL sorts
NULL values according to the ANSI SQL
specification, and thus always places them first in the sort order,
regardless of whether or not you specify DESC.
Despite these differences, if you want NULL values
at one end or the other of the sort order, you can force them to be
placed where you want no matter which version of MySQL
you're using. Suppose you have a table
t with the following contents:
mysql> SELECT val FROM t;
+------+
| val |
+------+
| 3 |
| 100 |
| NULL |
| NULL |
| 9 |
+------+
Normally, sorting puts the NULL values at the
beginning:
mysql> SELECT val FROM t ORDER BY val;
+------+
| val |
+------+
| NULL |
| NULL |
| 3 |
| 9 |
| 100 |
+------+
To put them at the end instead, introduce an extra
ORDER BY column that maps
NULL values to a higher value than
non-NULL values:
mysql> SELECT val FROM t ORDER BY IF(val IS NULL,1,0), val;
+------+
| val |
+------+
| 3 |
| 9 |
| 100 |
| NULL |
| NULL |
+------+
That works for DESC sorts as well:
mysql> SELECT val FROM t ORDER BY IF(val IS NULL,1,0), val DESC;
+------+
| val |
+------+
| 100 |
| 9 |
| 3 |
| NULL |
| NULL |
+------+
If you find MySQL putting NULL values at the end
of the sort order and you want them at the beginning, use the same
technique, but reverse the second and third arguments of the
IF( ) function to map NULL
values to a lower value than non-NULL values:
IF(val IS NULL,0,1)
| 
No comments:
Post a Comment