null判定を含むSQLのcaseの書き方
SQLのcaseは列の値によって結果を出し分けたいときに便利な書き方である。
列名がwhenの外側かにくるか内側にくるかで大きく分けて2つの書き方があり、前者をcase式、後者をcase文と呼ぶ向きもある。*1
case <列名> when <条件1(「= 特定の値」など)> then <表示したい値1> when <条件2> then <表示したい値2> … else <その他の場合に表示したい値> end as <表示したい列名>
case when <列名> <条件1> then <表示したい値1> when <列名> <条件2> then <表示したい値2> … else <その他の場合に表示したい値> end as <表示したい列名>
対象の列に対してnull判定をしたい場合、「<列名> is null」という条件をひと続きで書かなければならない。
このため、null判定のほかにも列の値によって結果を出し分けたいなら、普通に考えると列名がwhenの内側にくる書き方にすることになる。
case when <列名> is null then <表示したい値1> when <列名> <条件2> then <表示したい値2> … else <その他の場合に表示したい値> end as <表示したい列名>
この書き方だと、列名を繰り返す書くことになり、条件が増えれば増えるほど煩雑になってしまう。
たとえば、近畿地方の府県を表す数値に応じて都道府県名を表示し、nullの場合は「不明」と表示したい場合、次のようにprefecture_idが6回繰り返される。
case when prefecture_id = 1 then '大阪府' when prefecture_id = 2 then '兵庫県' when prefecture_id = 3 then '京都府' when prefecture_id = 4 then '滋賀県' when prefecture_id = 5 then '奈良県' when prefecture_id = 6 then '和歌山県' when prefecture_id is null then '不明' end as 'prefecture_name'
なんとかprefecture_idを繰り返す回数を減らせないかと調べたところ、二重caseを使えばやりたいことに近いことができた。
case prefecture_id is null when 1 then '不明' else case prefecture_id when 1 then '大阪府' when 2 then '兵庫県' when 3 then '京都府' when 4 then '滋賀県' when 5 then '奈良県' when 6 then '和歌山県' end end as 'prefecture_name'
さて、これで見た目がすっきりして可読性が上がるかと思ったのだが、二重caseになっているし微妙か…
どちらかというと、caseの後ろにくる条件式の真偽で場合分けができる点を押さえておきたい。
*1:厳密にいえば「条件」とは以下での「列名」と「条件」を合わせたものである