Very often we need to sort some data by specific field in db table but in a way that NULL values goes at the end of dataset. Typical example is frontend which displays data from specific table, with sortable links at the top where you can make asc/desc sorting simple with one click of the mouse.
Since I am mostly dealing with domains, I’ve decided to create one MySQL table with few domains, simple enough to show solution for this problem.
mysql> select * from domains; +----+-------------------+-------------+ | id | domain | expire_date | +----+-------------------+-------------+ | 1 | dinke.net | 2010-01-17 | | 2 | lampix.net | 2009-12-26 | | 3 | blogodak.com | 2010-09-08 | | 4 | maestrodesert.com | 2009-09-11 | | 5 | unexistant.com | NULL | +----+-------------------+-------------+ 5 rows in set (0.00 sec)
So problem is, I want to sort data by expire_data field but in a way that NULL field (ie domain which is not regged yet or has expired) always goes at the end. By default NULL fields goes on the top if we sort by acceding (asc) order or at the end if we sort in descending (desc) order.
mysql> select * from domains order by expire_date asc; +----+-------------------+-------------+ | id | domain | expire_date | +----+-------------------+-------------+ | 5 | unexistant.com | NULL | | 4 | maestrodesert.com | 2009-09-11 | | 2 | lampix.net | 2009-12-26 | | 1 | dinke.net | 2010-01-17 | | 3 | blogodak.com | 2010-09-08 | +----+-------------------+-------------+ 5 rows in set (0.00 sec) mysql> select * from domains order by expire_date desc; +----+-------------------+-------------+ | id | domain | expire_date | +----+-------------------+-------------+ | 3 | blogodak.com | 2010-09-08 | | 1 | dinke.net | 2010-01-17 | | 2 | lampix.net | 2009-12-26 | | 4 | maestrodesert.com | 2009-09-11 | | 5 | unexistant.com | NULL | +----+-------------------+-------------+ 5 rows in set (0.00 sec)
We are going to solve this problem by using MySQL IF function, and solution is:
mysql> select * from domains order by if(expire_date is null, 1, 0), expire_date asc; +----+-------------------+-------------+ | id | domain | expire_date | +----+-------------------+-------------+ | 4 | maestrodesert.com | 2009-09-11 | | 2 | lampix.net | 2009-12-26 | | 1 | dinke.net | 2010-01-17 | | 3 | blogodak.com | 2010-09-08 | | 5 | unexistant.com | NULL | +----+-------------------+-------------+ 5 rows in set (0.00 sec)
MySQL IF function is similar as ternary operator, it returns value of first argument in case when expression evaluate to true, otherwise it returns value of 2nd argument. So in this particular example it returns 1 for NULL values and 0 for the rest, allowing us to have NULL values at the end of the list.
I know that this might sound little bit confusing so we have to go one step further and add another one field in our MySQL table in order to explain what is exactly going on here.
mysql> alter table domains add column nullorder tinyint not null; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
then we are going to update values of nullorder field so they contains values of IF expression above:
mysql> update domains set nullorder = if(expire_date is null, 1, 0); Query OK, 1 row affected (0.00 sec) Rows matched: 5 Changed: 1 Warnings: 0 mysql> select * from domains; +----+-------------------+-------------+-----------+ | id | domain | expire_date | nullorder | +----+-------------------+-------------+-----------+ | 1 | dinke.net | 2010-01-17 | 0 | | 2 | lampix.net | 2009-12-26 | 0 | | 3 | blogodak.com | 2010-09-08 | 0 | | 4 | maestrodesert.com | 2009-09-11 | 0 | | 5 | unexistant.com | NULL | 1 | +----+-------------------+-------------+-----------+ 5 rows in set (0.00 sec)
Now when we have nullorder field our first query:
select * from domains order by if(expire_date is null, 1, 0), expire_date asc;
can also be written in this way:
mysql> select * from domains order by nullorder, expire_date; +----+-------------------+-------------+-----------+ | id | domain | expire_date | nullorder | +----+-------------------+-------------+-----------+ | 4 | maestrodesert.com | 2009-09-11 | 0 | | 2 | lampix.net | 2009-12-26 | 0 | | 1 | dinke.net | 2010-01-17 | 0 | | 3 | blogodak.com | 2010-09-08 | 0 | | 5 | unexistant.com | NULL | 1 | +----+-------------------+-------------+-----------+ 5 rows in set (0.00 sec)
So as you can see completely the same stuff as first one, except that we didn’t have to create nullorder field.
Of course in similar way you can have NULL fields appear at the beginning of the list (in case of desc order) when you need something like that.