How To Order NULL Values First Or Last In MySQL?

n 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:

1. Ordering NULL values last whilst sorting all non-NULL values first in an ascending order;
2. 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 NULLs 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 0otherwise. 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 NULLcomparison 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 NULLs First

Using The IS NOT NULL Comparison Operator:

Similar to the IS NULL operator, we can rely on the fact that IS NOT NULLreturns 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 |
+----+--------+------------+