前のブログを削除する予定なので、こちらに移動。
ソートするとNULLがDBMS依存でソートされる問題
こういうテーブルがあります。これはidの順にソートしてます。
[sql]SELECT * FROM table ORDER BY id[/sql]
| id | name |
|---|---|
| 1 | Hitomi |
| 2 | Shida |
| 3 | NULL |
これを、nameの順にソートすると
[sql]SELECT * FROM table ORDER BY name[/sql]
| id | name |
|---|---|
| 3 | NULL |
| 1 | Hitomi |
| 2 | Shida |
こうなります。NULLが最大値と解釈されているためです。Oracleだと最小値と解釈され、一番下にきます。NULLをどうソートするかはDBMS依存のようです。
現在のまとめ
- NULLをどうソートするかはDBMS依存
- MySQLではNULLが最大値と解釈されてソートされる
- OracleではNULLが最小値と解釈されてソートされる
MySQL4系：NULLを後ろにするにはCASE文を使う
昇順ソートでNULLを後ろに持ってきたいとき、MySQL4系では
[sql]ORDER BY CASE WHEN column_name IS NULL 最低値 ELSE column_name END[/sql]
こうすると、column_nameの値がNULLだったとき最低値が入った状態でソートされる
（name_kanaがNULLか空文字だったとき、カナの最低値であるヴヴヴヴを入れて強制的にソート順ASCの一番下にする）
[sql]SELECT *
FROM table
ORDER BY CASE
WHEN name_kana IS NULL OR name = ” THEN ‘ヴヴヴヴヴ’
ELSE name_kana END[/sql]
MySQL5系：NULLを最後にするにはIS NULLをORDER BYに
MySQL5系では、IS NULLをORDER BYに入れることでNULLを最後にできる（でも、DBMSの系がわからないなら４系のほうがエラー起きなくていいのかも。）
[sql]ORDER BY column_name IS NULL, column_name;[/sql]
Oracle：NULLをLAST・FIRSTに切り替えられる
ORACLEだとNULLは最大値扱いで、下記クエリはNULLをLASTにしたりFIRSTにしたりできる（便利ね）
[sql]ORDER BY column_name NULLS LAST;[/sql]
例：MySQL5でCASEを使って県名ソートする
例えば、県名が直接DBに入っていて、都道府県順にソートできない〜〜といったときにも使えます。
[sql]SELECT *
FROM table
ORDER BY
CASE
WHEN m.org_address_1 = ‘北海道’ THEN 1
WHEN m.org_address_1 = ‘青森県’ THEN 2
…[/sql]
なんかこうしたら北海道って入ってたら1とみなされてソートするからいいんじゃないかなと。
そもそもそんなテーブルいやだけど…