MySQLのORDER BYでNULLの優先度を下げる書き方【4パターン】

MySQLのNULLに対するORDER BYの取り扱いについて

MySQLにて、NULLが許容されたカラムに対してORDER BYをかける際にはちょっと注意した方が良いです。

何も気にせずにORDER BYをかけると想定と違う挙動になる場合もあるので、MySQLのNULLに対する挙動の整理とNULLに対する優先度を制御する方法をご紹介しようと思います。

NULLの取り扱いについてのおさらい

前提

今回は以下のテーブルを使って試しています。

usersテーブル

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NOT NULL COMMENT '名前',
  `age` varchar(255) COMMENT '年齢',
  `priority` int(10) COMMENT '優先度',
  `created_at` datetime NOT NULL COMMENT '登録日',
  PRIMARY KEY (`id`)
) COMMENT = '会員情報';

投入データはこんな感じ。

投入SQL

INSERT INTO `users` VALUES
(
	1,
	'Taro', -- name
	20, -- age
	2, -- priority
	NOW()-- created_at
),(
	2,
	'Jiro', -- name
	18, -- age
	NULL, -- priority
	NOW()-- created_at
),(
	3,
	'Saburo', -- name
	185, -- age
	1, -- priority
	NOW()-- created_at
);

通常のORDER BY

それでは、priorityカラムを軸にORDER BYして見ます。

ASC(昇順)の場合

まずはASCから確認して見ましょう。

SELECT * FROM users ORDER BY priority ASC;

+----+--------+------+----------+---------------------+
| id | name   | age  | priority | created_at          |
+----+--------+------+----------+---------------------+
|  2 | Jiro   | 18   |     NULL | 2020-03-30 13:46:03 |
|  3 | Saburo | 185  |        1 | 2020-03-30 13:46:03 |
|  1 | Taro   | 20   |        2 | 2020-03-30 13:46:03 |
+----+--------+------+----------+---------------------+
3 rows in set (0.00 sec)

結果としてはNULLが最大値として判定され、そのあとに値の昇順(0,1,2...)が判定されています。

DESC(降順)の場合

次にDESCで確認して見ましょう。

SELECT * FROM users ORDER BY priority DESC;

+----+--------+------+----------+---------------------+
| id | name   | age  | priority | created_at          |
+----+--------+------+----------+---------------------+
|  1 | Taro   | 20   |        2 | 2020-03-30 13:46:03 |
|  3 | Saburo | 185  |        1 | 2020-03-30 13:46:03 |
|  2 | Jiro   | 18   |     NULL | 2020-03-30 13:46:03 |
+----+--------+------+----------+---------------------+
3 rows in set (0.00 sec)

結果としては値の降順(100,99,98...)が判定されNULLが最小値となっています。

NULLの優先度を制御する方法

ORDER BY句の先頭にIS NULLを追加

上記で紹介したようなシンプルなORDER BYとは違う順序制御をしたい場合は、ORDER BY句の先頭にORDER BY {対象項目} IS NULL {ASC or DESC},{対象項目}とするだけで制御が可能になります。

[adsense-responsive]

NULLの優先度を最低、値を昇順にしたい場合

以下のSQLで実現可能です。

SELECT * FROM users ORDER BY priority IS NULL ASC, priority ASC;

+----+--------+------+----------+---------------------+
| id | name   | age  | priority | created_at          |
+----+--------+------+----------+---------------------+
|  3 | Saburo | 185  |        1 | 2020-03-30 13:46:03 |
|  1 | Taro   | 20   |        2 | 2020-03-30 13:46:03 |
|  2 | Jiro   | 18   |     NULL | 2020-03-30 13:46:03 |
+----+--------+------+----------+---------------------+
3 rows in set (0.00 sec)

期待通りの並び順になりましたね♪

上記の構文を解説すると、まず値がNULLかどうかの判定を行いNULLじゃない場合をFALSE(=0)と判定し、その値でASCをかけることにより値が登録されているレコードを優先的に取得し、そのあとに値がNULLの場合のレコードを昇順で取得するようにしています。

NULLの優先度を最高、値を降順にしたい場合

以下のSQLで実現可能です。

SELECT * FROM users ORDER BY priority IS NULL DESC, priority DESC;

+----+--------+------+----------+---------------------+
| id | name   | age  | priority | created_at          |
+----+--------+------+----------+---------------------+
|  2 | Jiro   | 18   |     NULL | 2020-03-30 13:46:03 |
|  1 | Taro   | 20   |        2 | 2020-03-30 13:46:03 |
|  3 | Saburo | 185  |        1 | 2020-03-30 13:46:03 |
+----+--------+------+----------+---------------------+
3 rows in set (0.00 sec)

こちらも期待通りの結果になりました。

上記の構文を解説すると、まず値がNULLかどうかの判定を行いNULLじゃない場合をFALSE(=0)と判定し、その値でDESCをかけることにより値がNULLの場合のレコードを優先的に取得し、そのあとに値が登録されているレコードを降順で取得するようにしています。

終わりに

以上のようにちょっと取り扱いが難しいMySQLのNULLに対するORDER BYテクニックでした。

今回ご紹介した4パターンを覚えておけば、NULLが混在したカラムに対するORDER BYは網羅出来ると思うので、お困りの方はぜひ試して見てください♪