MySQLでNULLを下にASCソートする このエントリーをはてなブックマークに追加 

24 8月

前のブログを削除する予定なので、こちらに移動。

ソートするとNULLがDBMS依存でソートされる問題

こういうテーブルがあります。これはidの順にソートしてます。

SELECT * FROM table ORDER BY id
id name
1 Hitomi
2 Shida
3 NULL

これを、nameの順にソートすると

SELECT * FROM table ORDER BY name
id name
3 NULL
1 Hitomi
2 Shida

こうなります。NULLが最大値と解釈されているためです。Oracleだと最小値と解釈され、一番下にきます。NULLをどうソートするかはDBMS依存のようです。

現在のまとめ

  • NULLをどうソートするかはDBMS依存
  • MySQLではNULLが最大値と解釈されてソートされる
  • OracleではNULLが最小値と解釈されてソートされる

MySQL4系:NULLを後ろにするにはCASE文を使う

昇順ソートでNULLを後ろに持ってきたいとき、MySQL4系では

ORDER BY CASE WHEN column_name IS NULL 最低値 ELSE column_name END

こうすると、column_nameの値がNULLだったとき最低値が入った状態でソートされる

(name_kanaがNULLか空文字だったとき、カナの最低値であるヴヴヴヴを入れて強制的にソート順ASCの一番下にする)

SELECT *
FROM table
ORDER BY CASE
WHEN name_kana IS NULL OR name = '' THEN 'ヴヴヴヴヴ'
ELSE name_kana END

MySQL5系:NULLを最後にするにはIS NULLをORDER BYに

MySQL5系では、IS NULLをORDER BYに入れることでNULLを最後にできる(でも、DBMSの系がわからないなら4系のほうがエラー起きなくていいのかも。)

ORDER BY column_name IS NULL, column_name;

Oracle:NULLをLAST・FIRSTに切り替えられる

ORACLEだとNULLは最大値扱いで、下記クエリはNULLをLASTにしたりFIRSTにしたりできる(便利ね)

ORDER BY column_name NULLS LAST;

例:MySQL5でCASEを使って県名ソートする

例えば、県名が直接DBに入っていて、都道府県順にソートできない〜〜といったときにも使えます。

SELECT *
FROM table
ORDER BY
CASE
WHEN m.org_address_1 = '北海道' THEN 1
WHEN m.org_address_1 = '青森県' THEN 2
...

なんかこうしたら北海道って入ってたら1とみなされてソートするからいいんじゃないかなと。
そもそもそんなテーブルいやだけど…

No comments yet

Leave a Reply