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 |
| …
[Read more]