NULLS LAST
Since there is no "NULLS LAST" syntax in MySQL, experienced Oracle DBAs must sort the null values to the last in another ways, here are several equivalent ways to do it:
For example, I'd like to order the "weight" column ascend, but put the nulls last.
mysql> select product_name, weight from products order by 2;
+--------------+--------+
| product_name | weight |
+--------------+--------+
| TV | NULL |
| NB | 1 |
| PC | 2 |
| PR | 3 |
+--------------+--------+
4 rows in set (0.01 sec)
Most of rows are ordered as I wish, except the first row with nulled value.
Let try to sort them descend.
mysql> select product_name, weight from products order by 2 desc;
+--------------+--------+
| product_name | weight |
+--------------+--------+
| PR | 3 |
| PC | 2 |
| NB | 1 |
| TV | NULL |
+--------------+--------+
4 rows in set (0.00 sec)
Nope, this is not what we want, neither.
Of course, we can change the NULL into a solid value by coalescing.
mysql> select product_name, coalesce(display_order, 999999) weight from products order by 2;
+--------------+--------+
| product_name | weight |
+--------------+--------+
| NB | 1 |
| PC | 2 |
| PR | 3 |
| TV | 999999 |
+--------------+--------+
4 rows in set (0.00 sec)
But the drawback is that we changed the output values.
Order by COALESCE()
Alternatively, you can put coalesce in the order clause.
mysql> select product_name, weight from products order by coalesce(weight, 999999);
+--------------+--------+
| product_name | weight |
+--------------+--------+
| NB | 1 |
| PC | 2 |
| PR | 3 |
| TV | NULL |
+--------------+--------+
4 rows in set (0.00 sec)
It's lots better, so far, we should satisfy with this, but it can not cover all the situations (e.g. sorting a character-based column), even you use a very large value.
~ (Tilde)
Please use ~ (Tilde) character as the substitute to put the NULLs to the last. It can cover more situations if you are not too picky.
mysql> select product_name, weight from products order by coalesce(weight, '~');
+--------------+--------+
| product_name | weight |
+--------------+--------+
| NB | 1 |
| PC | 2 |
| PR | 3 |
| TV | NULL |
+--------------+--------+
4 rows in set (0.00 sec)