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)

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net