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