【MySQL】ORDER BYで並べ替えたとき、NULLが表示される位置を変更したい

はじめに

MySQLでNULLを含む列をソートキーにして並べ替えをすると、NULLは昇順(ASC)だと先頭に、降順(DESC)だと末尾にまとめて表示されます。

sql

SELECT product_name, cost
FROM products
ORDER BY cost;

昇順だとNULLは先頭に

+--------------+------+
| product_name | cost |
+--------------+------+
| towel        | NULL |
| bottle       | NULL |
| soap         |  220 |
| brush        |  380 |
| cup          |  550 |
+--------------+------+

sql

SELECT product_name, cost
FROM products
ORDER BY cost DESC;

降順だとNULLは末尾に

+--------------+------+
| product_name | cost |
+--------------+------+
| cup          |  550 |
| brush        |  380 |
| soap         |  220 |
| towel        | NULL |
| bottle       | NULL |
+--------------+------+

MySQLではNULLS FIRST / NULLS LASTは使えない

PostgreSQLやOracleなどでは、次のようにオプションを使用してNULLの表示される位置を変更することができます。

sql

-- PostgreSQLの場合
SELECT product_name, cost
FROM products
ORDER BY cost DESC NULLS LAST;

(PostgreSQL)デフォルトだと降順で先頭に表示されるNULLを、オプションで末尾に変更

 product_name | cost 
--------------+------
 cup          |  550
 brush        |  380
 soap         |  220
 towel        |     
 bottle       |     

しかし、MYSQLではこの構文はサポートされていません。
実行すると構文エラーになります。

NULLが表示される位置を変更する方法(MySQL)

MySQLでNULLが表示される位置を変更するには、以下のように記述します。

sql：昇順で並べ替えたときNULLを末尾に

SELECT product_name, cost
FROM products
ORDER BY cost IS NULL ASC, cost ASC;

結果

+--------------+------+
| product_name | cost |
+--------------+------+
| soap         |  220 |
| brush        |  380 |
| cup          |  550 |
| towel        | NULL |
| bottle       | NULL |
+--------------+------+

ORDER BY cost IS NULL ASCは何をしている？

実際にcost IS NULLの結果を見てみると

sql

SELECT product_name, cost IS NULL
FROM products;

+--------------+--------------+
| product_name | cost is null |
+--------------+--------------+
| cup          |            0 |
| towel        |            1 |
| soap         |            0 |
| brush        |            0 |
| bottle       |            1 |
+--------------+--------------+

• costに値がある→0(FALSE)
• costがNULL→1(TRUE)

となっています。

つまり、

sql

ORDER BY cost IS NULL ASC

は、

• 値あり(0)を先に
• NULL(1)を後に
  並べるという意味になります。
  そのあとでcost ASCを指定することで、NULL以外の値同士を昇順に並べているということです。

ご参考になれば嬉しいです🙌