DB側で条件分岐をしたい時

　今まで条件によって取得する値を変えたい時は、無条件にPHP側で条件分岐を作成して、それぞれの場合の値を取得するSQLを書いていました。今回、PHP側でなくSQL側で条件分岐を書けること、場合によってはその方がパフォーマンスも良くなることを知ったので、未来の自分に向けてまとめてみました。

CASE式の基本

SQLで条件分岐をしたい時にとても便利なのがCASE式です。
CASE式は以下2つの書き方ができます。

例１

CASE color
    WHEN 'white' THEN '白'
    WHEN 'black' THEN '黒'
    ELSE NULL
END

例２

CASE
    WHEN color = 'white' THEN '白'
    WHEN color = 'black' THEN '黒'
    ELSE NULL
END

　例１、例２はそれぞれ単純CASE式、検索CASE式と呼ばれ、どちらを使っても良いのですが、検索CASE式の方が書ける条件が多いので、迷ったら検索CASE式を使った方が良いようです。
　ELSE NULLは省略可能で、省略した場合は暗黙的にELSE NULLとして解釈されます。ただ、うっかり書き忘れた時に意図せずNULLを返してしまったり、他の人がコードを読んだ時にNULLが返ってくることが一見してわかりづらいので、省略せずに常に書く習慣をつけた方が良いです。
　また、CASE式の終わりにENDを書き忘れるのもよくあるミスらしいですが、こちらは省略不可のためエラーになってしまうのでお気をつけください。

CASE式を使った具体例

実際にどのような使い方ができるか、いくつか具体例を作ってみました。

年齢によって成人/未成年を判定する

customer_informationテーブル

id	name	age
1	佐藤	20
2	鈴木	16
3	田中	NULL

SELECT
    id,
    name,
    CASE
        WHEN age >= 18 THEN '成人'
        WHEN age < 18 THEN '未成年'
        ELSE '不明'
    END AS age
FROM customer_information;

実行結果

id	name	age
1	佐藤	成人
2	鈴木	未成年
3	田中	不明

都道府県から地域を判定する

addresテーブル

zip_code	prefecture	city	street_address
5300001	大阪	大阪市	梅田1丁目
6000001	京都	京都市	四条通
7600001	香川	高松市	中央通り
7700001	徳島	徳島市	駅前通り
1000001	東京	千代田区	千代田1丁目

SELECT
    zip_code,
    CASE
        WHEN prefecture = '大阪' THEN '関西'
        WHEN prefecture = '京都' THEN '関西'
        WHEN prefecture = '香川' THEN '四国'
        WHEN prefecture = '徳島' THEN '四国'
        ELSE 'その他'
    END
    AS region,
    city,
    street_address
FROM address

実行結果

zip_code	region	city	street_address
5300001	関西	大阪市	梅田1丁目
6000001	関西	京都市	四条通
7600001	四国	高松市	中央通り
7700001	四国	徳島市	駅前通り
1000001	その他	千代田区	千代田1丁目

動物の種類ごとに頭数をカウントする

animalテーブル

id	spacies
1	犬
2	犬
3	猫
4	犬
5	猫

SELECT
    SUM(CASE WHEN species = '犬' THEN 1 ELSE 0 END) AS number_of_dogs,
    SUM(CASE WHEN spacies = '猫' THEN 1 ELSE 0 END) AS number_of_cats
FROM animals

実行結果

number_of_dogs	number_of_cats
3	2

まとめ

このようにCASE式だけでも本当にいろいろなことができます！
先日記事にまとめたように、SQLで値の置換もすることができます。

PHPとSQL両方を学ぶことで、課題の対処方法の引き出しがもっと増やせそうだと思いました。

参考文献