In my post yesterday, I shared a little known trick
for sorting NULLs last when using ORDER BY ASC.

To summarize briefly, NULLs are treated as less than 0 when used
in ORDER BY, However, sometimes you do not want that behavior,
and you need the NULLs listed last, even though you want your
numbers in ascending order.

So a query like the following returns the NULLs first (expected
behavior):

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.

If you, like me, are building or thinking of implementing a
MySQL-powered application that has any need for prioritizing
selecting certain data over other data, this article is for you.

Example

As a real world example, consider a queue-like video processing
system. Your application receives new videos and processes them.
The volume of incoming videos can at times be higher than the
processing rate because the process is CPU bound, so occasionally
a pretty long queue may form. You will try to process them as
fast as you can but…

Note that I am using a queue here, so the the next item
to be processed is a result of sorting by some sort of field in a
ascending order, for example ORDER BY id or
ORDER BY upload_date. I’ll pick the id sort here.

…suddenly, you need to process a video somewhere in the middle of
the queue or an important video enters and needs …

Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.