【MySQL】 ソートで NULL を一番最後にするとき，IS NULL ASC としてはいけない理由．

少々挑戦的なタイトルですが，本当は IS NULL ASC で全然構いません．（笑）

ただ私が言いたいのは，魔法の呪文のように意味も分からず IS NULL ASC とするのはやめたほうが良い．
より厳密には，それだけ覚えてしまうのは勿体無い．ということです．

IS NULL ASC のように ORDER BY 句を用いれば，様々な条件でソートすることができるので，この記事では，
なぜ IS NULL ASC で NULL を後ろにできるのか実演しながら，より汎用的な条件付きソートについて考えていきます．

サンプルとして以下のようなテーブルを用意しました．（例によってラズパイを使っているので MariaDBですが，基本的に MySQL でも同じです）

 MariaDB [sample]> SELECT * FROM SAMPLE;
 +------+-------+
 | id   | value |
 +------+-------+
 |    1 |     0 |
 |    2 |    10 |
 |    3 |     4 |
 |    4 |  NULL |
 |    5 |     8 |
 |    6 |     1 |
 |    7 |     3 |
 |    8 |     2 |
 |    9 |  NULL |
 |   10 |    -5 |
 +------+-------+

これを普通に ORDER BY するとこうなります．

 MariaDB [sample]> SELECT * FROM SAMPLE ORDER BY value;
 +------+-------+
 | id   | value |
 +------+-------+
 |    9 |  NULL |
 |    4 |  NULL |
 |   10 |    -5 |
 |    1 |     0 |
 |    6 |     1 |
 |    8 |     2 |
 |    7 |     3 |
 |    3 |     4 |
 |    5 |     8 |
 |    2 |    10 |
 +------+-------+

このとき NULL を一番後ろにするには，ORDER BY に IS NULL ASC を付けると良い．というのがよく見る解説です．

 MariaDB [sample]> SELECT * FROM SAMPLE ORDER BY value IS NULL ASC, value;
 +------+-------+
 | id   | value |
 +------+-------+
 |   10 |    -5 |
 |    1 |     0 |
 |    6 |     1 |
 |    8 |     2 |
 |    7 |     3 |
 |    3 |     4 |
 |    5 |     8 |
 |    2 |    10 |
 |    4 |  NULL |
 |    9 |  NULL |
 +------+-------+

これが冒頭で魔法の呪文に例えた部分ですが，実のところ，これは単に ORDER BY 句で２つの要素を指定してソートしているだけです．

ここで言う，２つの要素とは，

• value IS NULL
• value

の２つ．そして，前者は明示的に昇順（Ascent：ASC），後者は暗黙（デフォルト）で昇順（Ascent：ASC）としています．

まだ良くわからない．という人は次の結果を見てください．（太字箇所の類似性に注目）

 MariaDB [sample]> SELECT id, value IS NULL, value FROM SAMPLE ORDER BY value IS NULL ASC, value;
 +------+---------------+-------+
 | id   | value IS NULL | value |
 +------+---------------+-------+
 |   10 |             0 |    -5 |
 |    1 |             0 |     0 |
 |    6 |             0 |     1 |
 |    8 |             0 |     2 |
 |    7 |             0 |     3 |
 |    3 |             0 |     4 |
 |    5 |             0 |     8 |
 |    2 |             0 |    10 |
 |    4 |             1 |  NULL |
 |    9 |             1 |  NULL |
 +------+---------------+-------+

いかがでしょうか．

つまり，value IS NULL は value が NULL のとき1,それ以外のとき0を返すような条件分で，それを昇順にソートしつつ，value でも昇順にソートしているわけです．

以上を踏まえると，例えば，NULL または負の数を後ろにしたいときは，以下のように条件を組み合わせます．

 MariaDB [sample]> SELECT id, value IS NULL, value < 0, value FROM SAMPLE ORDER BY value IS NULL ASC, value < 0 ASC, value;
 +------+---------------+-----------+-------+
 | id   | value IS NULL | value < 0 | value |
 +------+---------------+-----------+-------+
 |    1 |             0 |         0 |     0 |
 |    6 |             0 |         0 |     1 |
 |    8 |             0 |         0 |     2 |
 |    7 |             0 |         0 |     3 |
 |    3 |             0 |         0 |     4 |
 |    5 |             0 |         0 |     8 |
 |    2 |             0 |         0 |    10 |
 |   10 |             0 |         1 |    -5 |
 |    4 |             1 |      NULL |  NULL |
 |    9 |             1 |      NULL |  NULL |
 +------+---------------+-----------+-------+

便利ですが，だんだん混乱してきますね．

そもそも，条件を付けるときは，手前に持ってきたい値の条件を考えるのが直感的だと私は思います．
今回の例で言うならば，NULLでなく，0以上のものを手前に抽出したい，と．

なので，そうします．

 MariaDB [sample]> SELECT id, value IS NOT NULL, value >= 0, value FROM SAMPLE ORDER BY value IS NOT NULL DESC, value >= 0 DESC, value;
 +------+-------------------+------------+-------+
 | id   | value IS NOT NULL | value >= 0 | value |
 +------+-------------------+------------+-------+
 |    1 |                 1 |          1 |     0 |
 |    6 |                 1 |          1 |     1 |
 |    8 |                 1 |          1 |     2 |
 |    7 |                 1 |          1 |     3 |
 |    3 |                 1 |          1 |     4 |
 |    5 |                 1 |          1 |     8 |
 |    2 |                 1 |          1 |    10 |
 |   10 |                 1 |          0 |    -5 |
 |    4 |                 0 |       NULL |  NULL |
 |    9 |                 0 |       NULL |  NULL |
 +------+-------------------+------------+-------+

条件一致が1，不一致が0なので，手前に抽出したい条件を指定して，降順（Descent：DESC）にすれば良いわけです．

というわけでタイトル回収．

ソートで NULL を一番最後にするとき，IS NULL ASC ではなく，IS NOT NULL DESC を用いましょう．

余計に魔法の呪文のようになってしまったと言えなくもないですが，考えて条件を指定するときは直感的になると思います．