Treating NULLs as not less than zero in ORDER BY

I was working on a seemingly basic query the other day where the user needed to have an INT column listed in ascending order (i.e., 1, 2, 3, …).

However, the tricky part came in because the column allowed NULLs and the user needed the NULLs to be listed last, not first, which is the default behavior in both MariaDB and MySQL.

We first devised a somewhat convoluted solution where we used ISNULL() first in the ORDER BY, and then the column, but that wasn’t ideal since it added an additional check for each row in the ORDER BY, which we wanted to avoid in a query returning ~5M rows.

To illustrate, a normal query just sorting in ASC order returned:

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, col3 ASC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | yellow | NULL |
| apple  | red    |    5 |
| apple  | green  |   10 |
| banana | brown  | NULL |
| banana | green  |    5 |
| banana | yellow |   10 |
+--------+--------+------+

But of course we want the NULLs last in each respective group, so we first added an ISNULL() check, which does return what we need:

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, ISNULL(col3) ASC, col3 ASC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | red    |    5 |
| apple  | green  |   10 |
| apple  | yellow | NULL |
| banana | green  |    5 |
| banana | yellow |   10 |
| banana | brown  | NULL |
+--------+--------+------+

However, ideally, we wanted to eliminate the ISNULL() call.

The solution was to use a little know trick (at least to me, and I also don’t see it documented) where you add a minus sign (“-“) in front of the column you want to sort and also change the ASC to DESC. The minus sign is essentially the inverse, hence the change from ASC to DESC, but it has the added benefit (in this case) of now listing NULLs last.

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, -col3 DESC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | red    |    5 |
| apple  | green  |   10 |
| apple  | yellow | NULL |
| banana | green  |    5 |
| banana | yellow |   10 |
| banana | brown  | NULL |
+--------+--------+------+

Hope this helps.