MySQLとPostgreSQLではnullを含んだカラムでソートするときのnullの扱いが違う。 MySQLでnullは、nullでない値よりも小さな値としてソートされ、PostgreSQLでは、nullでない値よりも大きな値としてソートされる。
そのnullの扱いが自分の意図したものと異なる場合や、データベースを変更する場合などに困ったことになることもある。
MySQLとPostreSQLのnullの扱いの違い
例えば、以下のようなデータがあるとする。
> SELECT * FROM counter; +----+-------+ | id | count | +----+-------+ | 1 | NULL | | 2 | 4 | | 3 | 5 | | 4 | 1 | | 5 | 5 | | 6 | NULL | | 7 | 5 | | 8 | 3 | | 9 | 2 | | 10 | 3 | +----+-------+ 10 rows in set (0.00 sec)
id=1とid=6のデータがnullになっている。
これをMySQLでソートすると以下のようになる。
> SELECT * FROM counter ORDER BY count; +----+-------+ | id | count | +----+-------+ | 1 | NULL | | 6 | NULL | | 4 | 1 | | 9 | 2 | | 8 | 3 | | 10 | 3 | | 2 | 4 | | 3 | 5 | | 5 | 5 | | 7 | 5 | +----+-------+ 10 rows in set (0.00 sec)
PostgreSQLでは以下のようになる。
> SELECT * FROM counter ORDER BY count; id | count ----+------- 4 | 1 9 | 2 8 | 3 10 | 3 2 | 4 3 | 5 5 | 5 7 | 5 1 | 6 | (10 rows)
MySQLではnullが上に、PostgreSQLでは下にくる。
PostgreSQLと同じようにソートする方法１［ORDER BY (CASE WHEN ...)］
まず１つ目の方法は、CASE WHENを使う方法だ。 これを使ってcountがnullだった場合は2147483647（4バイトint最大値）として扱ってソートする。
> SELECT * FROM counter ORDER BY (CASE WHEN count IS NULL THEN 2147483647 ELSE count END); +----+-------+ | id | count | +----+-------+ | 4 | 1 | | 9 | 2 | | 10 | 3 | | 8 | 3 | | 2 | 4 | | 3 | 5 | | 5 | 5 | | 7 | 5 | | 6 | NULL | | 1 | NULL | +----+-------+ 10 rows in set (0.01 sec)
以下のように、(CASE WHEN ...)でソートした後に、countでソートする方法もある。
> SELECT * FROM counter ORDER BY (CASE WHEN count IS NULL THEN 1 ELSE 0 END), count; +----+-------+ | id | count | +----+-------+ | 4 | 1 | | 9 | 2 | | 10 | 3 | | 8 | 3 | | 2 | 4 | | 3 | 5 | | 5 | 5 | | 7 | 5 | | 6 | NULL | | 1 | NULL | +----+-------+ 10 rows in set (0.00 sec)
ただ、この方法を使うのなら後述する方法２の方が簡単に書ける。
PostgreSQLと同じようにソートする方法２［ORDER BY column IS NOT NULL, column］
以下のようにすれば、CASE WHENを使うよりも簡単に書くことができる。
> SELECT * FROM counter ORDER BY count IS NULL, count; +----+-------+ | id | count | +----+-------+ | 4 | 1 | | 9 | 2 | | 10 | 3 | | 8 | 3 | | 2 | 4 | | 3 | 5 | | 5 | 5 | | 7 | 5 | | 6 | NULL | | 1 | NULL | +----+-------+ 10 rows in set (0.00 sec)
