CASE式について

今日、「達人に学ぶSQL徹底指南書」という本を買って読み始めました。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

最初の章でCASE式のことが取り上げられていて、ためになったのでメモしておきます。

CASE式をEXISTSやINと併用するとかっこいい☆

例えば、「友達と一緒に帰った日」を以下のテーブルで保持していたとき、
[friend:友達]

  1. id : int
  2. name : varchar

[go_home_with:帰宅ログ]

  1. day : date
  2. friend_id : int(FK->Friend)

友達を縦軸、日付を横軸にとって検索ができます。

select name,
    case when exists (
        select 1 from go_home_with H
        where F.id = H.friend_id and H.day = "2011-10-31"
    ) then '○' else '×' end as "10/31",
    case when exists (
        select 1 from go_home_with H
        where F.id = H.friend_id and H.day = "2011-11-01"
    ) then '○' else '×' end as "11/01",
    case when exists (
        select 1 from go_home_with H
        where F.id = H.friend_id and H.day = "2011-11-02"
    ) then '○' else '×' end as "11/02"
from friend F
;

結果はこんな風になります。

+--------+-------+-------+-------+
| name   | 10/31 | 11/01 | 11/02 |
+--------+-------+-------+-------+
| ギイ   | ×     | ○     | ×     |
| 赤池   | ○     | ○     | ×     |
| 三洲   | ○     | ×     | ×     |
+--------+-------+-------+-------+

例えば10月31日にギイと一緒に帰れなかった(=レコードが存在しない)場合、検索結果がヒットしないんじゃなくて「×」って結果が取得できます。
こういう、「元々縦に並んでいる(行単位に管理しているデータ)ものを横に並べ直す(=列単位に整理)」ってこと、結構あるんです。
だから覚えておいて損はないです(そんなの、テーブル設計がダメ? まあそうかもしれません。)
同じことをEXISTSでなくINでもできます(ごめんなさい、こっちは割愛)。

CASE式のELSEを省略すると"ELSE NULL"と暗黙的に扱われるので注意

タイトルの通り、CASE式のELSEを省略するとNULLと扱われるそうです。
なので、

select
    id, name, salary old_salary,
    case
        when salary > 300000 then salary * 0.9
        when salary between 250000 and 280000 then salary * 1.2
        end new_salary
from emp
;

こんなSQLを書くと、

+----+------+------------+------------+
| id | name | old_salary | new_salary |
+----+------+------------+------------+
|  1 | foo  |     250000 |   300000.0 |
|  2 | bar  |     320000 |   288000.0 |
|  3 | baz  |     130000 |       NULL |
+----+------+------------+------------+

こういう結果が返ってくるので要注意です。
UPDATE文でこれと同じことをすると、(たぶん)bazのsalaryはそのままにしておきたいはずが、NULLに更新されてしまったら大変ですね。

なお、僕はこれをMySQLで動作確認しています。
Gist: https://gist.github.com/1338356