In MySQL NULL
values are considered lower than any non-NULL value, therefore, NULL
values appear first when the order is ASC
(ascending), and ordered last when the order is DESC
(descending). We'll be discussing the following two cases as sorting NULL
values in either of the cases might not be straightforward:
- Ordering
NULL
values last whilst sorting all non-NULL values first in an ascending order; - Ordering
NULL
values first followed by non-NULL values in descending order.
You can follow our guide and examples to discover how to achieve these and avoid unexpected results. For this article, we'll be using the following MySQL database table:
+----+--------+------------+ | id | name | date_login | +----+--------+------------+ | 1 | NULL | 2017-03-12 | | 2 | john | NULL | | 3 | david | 2016-12-24 | | 4 | zayne | 2017-03-02 | +----+--------+------------+
CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(5), date_login DATE, PRIMARY KEY (id) ); INSERT INTO user(name, date_login) VALUES (NULL, '2017-03-12'), ('john', NULL), ('david', '2016-12-24'), ('zayne', '2017-03-02');
Sort in Ascending Order With NULL
Values Last
Using the Minus Operator:
In MySQL NULL
values are considered lower in order than any non-NULL value, except if a -
(minus) character is added before the column name while sorting.
Query:
SELECT * FROM user ORDER BY -date_login DESC
While this may work well for numbers and dates, it may not be the best solution to sort fields with alpha or alphanumeric values, for those you may want to try the other methods discussed in this article.
Expected Result:
+----+--------+------------+ | id | name | date_login | +----+--------+------------+ | 3 | david | 2016-12-24 | | 4 | zayne | 2017-03-02 | | 1 | NULL | 2017-03-12 | | 2 | john | NULL | +----+--------+------------+
Using the IS NULL
Comparison Operator:
We can rely on the fact that IS NULL
returns 1
when expression is NULL, and 0
otherwise. With that information, we could do something like this:
Query:
SELECT * FROM user ORDER BY name IS NULL, name ASC
Using the ISNULL()
function is equivalent to using the IS NULL
comparison operator, so either one could be used.
Expected Result:
+----+--------+------------+ | id | name | date_login | +----+--------+------------+ | 3 | david | 2016-12-24 | | 2 | john | NULL | | 4 | zayne | 2017-03-02 | | 1 | NULL | 2017-03-12 | +----+--------+------------+
Using COALESCE
Function:
The COALESCE
function can also be used, which returns the first non-NULL value in a specified list of arguments (or NULL
if there are no non-NULL values). Therefore, to sort NULL
last whilst ordering non-NULL values in ascending order, we could simply supply a highest possible value as a fallback (or substitue) for NULL
values. For example:
Query:
SELECT * FROM user ORDER BY COALESCE(name, 'zz') ASC
This works because zee is the last letter in the alphabet, and by default MySQL sorts symbols and numbers first and then alphabets. Therefore, substituting NULL
values with highest possible sequence of the letter zee pushes NULL
values to the bottom of the list. In an integer-only column, however, we could just simply also use the highest number instead of a sequence of letter z.
The reason we used a sequence of two letter zee (i.e. zz
) in the query above, is to ensure that words that start with the letter z (for example the name zayne) remain above NULL
values.
Since under normal circumstances there may not be a case where a word starts with two letter zees appearing consecutively, the above query works well for the particular case. However, if there is an instance where a word may start with two or more letter zee appearing consecutively, then the highest possible sequence of the letter zee can be used. For example, if we consider zzz
as a possible value, then we can use zzzz
as an argument to the COALESCE
function – i.e. highest possible sequence of letter zee with one extra zee added at the end to ensure NULL
values sort last.
Expected Result:
+----+--------+------------+ | id | name | date_login | +----+--------+------------+ | 3 | david | 2016-12-24 | | 2 | john | NULL | | 4 | zayne | 2017-03-02 | | 1 | NULL | 2017-03-12 | +----+--------+------------+
Sort in Descending Order With NULL
Values First
Using the IS NOT NULL
Comparison Operator:
Similar to the IS NULL
operator, we can rely on the fact that IS NOT NULL
returns 1
when expression is NOT NULL, and 0
otherwise.
Query:
SELECT * FROM user ORDER BY date_login IS NOT NULL, date_login DESC
Using !ISNULL()
is equivalent to IS NOT NULL
, so either one could be used.
The same query could also be rewritten using IS NULL
like so:
SELECT * FROM user ORDER BY date_login IS NULL DESC, date_login DESC
Expected Result:
+----+--------+------------+ | id | name | date_login | +----+--------+------------+ | 2 | john | NULL | | 1 | NULL | 2017-03-12 | | 4 | zayne | 2017-03-02 | | 3 | david | 2016-12-24 | +----+--------+------------+
Using COALESCE
Function:
Query:
SELECT * FROM user ORDER BY COALESCE(name, 'zz') DESC
By simply sorting the result of the COALESCE
function in descending order, we make sure NULL
values appear first and all other values that appear right after are in descending order.
Expected Result:
+----+--------+------------+ | id | name | date_login | +----+--------+------------+ | 1 | NULL | 2017-03-12 | | 4 | zayne | 2017-03-02 | | 2 | john | NULL | | 3 | david | 2016-12-24 | +----+--------+------------+
This post was published (and was last revised ) by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.