Diamond Notes

Just another WordPress weblog

Tip of the Day — NULL Sort Order

March 06th, 2008 | Category: MySQL, Tip of the Day

The order that NULL is sorted in varies from database server to database server.  Some sort NULL first (in an ascending list), some sort NULL last in an ascending list.  As far as I know PostgreSQL, in the new version 8.3, is the only database server that allows you to specify which way the sort is applied.

When sorting your data it is important to know where NULL will fall when sorted.  Without further ado:

mysql> create table sort (id int not null, data varchar(5));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into sort (id,data) values (1,’a');
Query OK, 1 row affected (0.39 sec)

mysql> insert into sort (id,data) values (2,’b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (3,’c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (4,’c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (5,’d');
Query OK, 1 row affected (0.02 sec)

mysql> insert into sort (id,data) values (6,’e');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (7,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from sort;
+—-+——-+
| id    | data     |
+—-+——-+
|  1    | a          |
|  2    | b          |
|  3    | c          |
|  4    | c          |
|  5    | d          |
|  6    | e          |
|  7    | NULL |
+—-+——+
7 rows in set (0.00 sec)

mysql> select id, data from sort order by data asc;

+—-+——+
| id    | data   |
+—-+——+
|  7    | NULL |
|  1    | a          |
|  2    | b         |
|  3    | c          |
|  4    | c          |
|  5    | d         |
|  6    | e         |
+—-+——+
7 rows in set (0.24 sec)

mysql> select id, data from sort order by data desc;

+—-+——-+
| id    | data     |
+—-+——-+
|  6    | e          |
|  5    | d          |
|  3    | c          |
|  4    | c          |
|  2    | b          |
|  1    | a          |
|  7    | NULL |
+—-+——+
7 rows in set (0.00 sec)

mysql>

As you can see NULL is sorted before the other characters in the default (ascending) collation.  Just something to keep in mind!

9 Comments so far

  1. sapphirecat March 6th, 2008 10:58 am

    Are there any tricks to changing the way NULL sorts in MySQL? I’m aware of “ORDER BY ISNULL(data),data” but then (I think) you lose the ability to use indexes for the sorting.

  2. anonymous March 6th, 2008 11:21 am

    Firebird supports it

  3. Harrison Fisk March 6th, 2008 11:23 am

    PostgreSQL isn’t the first RDMBS to allow that. Firebird 1.5 has had the ability to do a ORDER BY… NULLS FIRST|LAST for a while.

  4. RST March 6th, 2008 11:29 am

    It gets more interesting when one tries to sort numeric values with negative numbers in it. Apparently, null is less than any negative number.

  5. admin March 6th, 2008 11:45 am

    RST, NULL is going to be first in any ascending sort list. Doesn’t matter what else is in the list.

  6. admin March 6th, 2008 11:48 am

    sapphirecat, none that I am aware of, but someone might correct me on this :)

  7. Ramiro Varandas Jr March 13th, 2008 10:33 am

    NULL is the first value of a sort ASC even in Excel or other spreasheet. It’s the default, since that column value doesn’t fit in an other category of sorting.

  8. admin March 13th, 2008 11:53 am

    Ramiro,

    My point to this post is that this varies from database vendor to database vendor. I don’t know anything about excel or any other spreadsheet for that matter. As an example, Oracle is the exact opposite of the NULL sort order in MySQL ref: (http://blogs.techrepublic.com.com/datacenter/?p=121).

  9. Jeremy May 1st, 2008 8:41 am

    I needed to do something similar, the site below shows how to do it. I’m not sure how it affects index searches as mentioned by sapphirecat.

    http://www.shawnolson.net/a/730/mysql-sort-order-with-null.html

Leave a reply