▶SQLで条件分岐を表現する
CASE式は、SQLで条件分岐を記述するためにぜひとも習得しておく必要のある重要かつ便利な技術です。この章では、行列変換、コード1本系の再分類、制約との組み合わせ、集約結果に対する条件分岐などの例題をもとに、CASE式の使い方を学びます。
はじめに
CASE式は、SQL-92で標準に取り人れられました。比較的新しい道具であるためか、便利なわりにその真価があまり知られておらず、利用されていなかったり、CASE式の簡略版であるDECODE(Oracle）、IF（MySQL)などの関数で代用されていたりします。しかし、セルコが－SQL-92で追加された中で最も有用かもしれない」と言うように、CASE式を活用するとSQLでできることの幅がぐっと広がり、書き方もスマートになります。しかも、実装非依存の技術ですから、コードの汎用性も高まります。特にDECODE関数を使っているOracleユーザーには、ぜひCASE式への乗り換えをお薦めします。
本章では、そんないいこといっばいのCASE式の便利な使い方を、具体例を通して学んでいきましょう。
導人：CASE式とは
まず、基本的な文法から解説しましょう。CASE式の書式には、単純CASE式（simple case）と検索CASE式（searched case）の2通りあります。それぞれ、次のように書きます。
■CASE式の書式
-－単純CASE式
CASE sex
WHEN '1' THEN ’男’
WHEN '2' THEN ’女’
ELSE ’その他’ END
-－検索CASE式
CASE WHEN sex= '1' THEN ’男’
WHEN sex= '2" THEN ’女'
ELSE ’その他’ END
この2つは、どちらも同じ動作をします。「性別（sex）」列が’1’なら「男」へ、’2’なら「女」へ読み替えているわけです。単純CASE式の方が、その名の通り簡潔に書けますが、できることも限られています。単純CASE式で書ける条件は、検索CASE式でも書くことができるので、本書でも基本的に、検索CASE式の方を多用します。また、CASE式の評価は、真になるWHEN句が見つかった時点で打ち切られて、残りのWHEN旬は無視されるので、そのことを意識してコーディングする必要があります。無用の混乱を避けるためにもWHEN句は排他的に記述するのが良いでしょう。
■残りのWHEN句が無視される記述例
-－例えば、こんなふうに書くと、結果には「2番」が現れない
CASE WHEN col_1 IN('a','b)')THEN '1番’
WHEN col_1 IN('a') THEN '2番’
ELSE ’その他’ END
また、CASE式を利用するときは、以下のようなポイントに気をつける必要があります。
注意点1：各分岐が返すデータ型を統一する
当然、といえば当然の制限なのですが、CASE式の返すデータ型は、全ての分岐において一致している必要があります。ある分岐では文字型を返し、別の分岐では数値型を返す、という書き方は認められていません。
注意点2:ENDの書き忘れに注意
CASE式を使うときの一番よくある文法的な間違いが、ENDの書き忘れです。もっとも、忘れた場合にも、比較的分かりやすいエラーメッセージが返ってくるので、それほど大きな問題になる間違いではありませんが、「構文は正しいのに動かない」と思ったときの半分はこの間違いによるので、よく注意しましょう。
注意点3:ELSE 句必ず書こう
先ほどのENDと違って、ELSE 句はオプションなので、書かなくてもエラーにはなりません。その場合、暗黙にELSE NULL」の扱いになります。しかし、「エラーにはならないけど、結果が違う」という厄介なバグの温床になるので、（たとえNULLでかまわない場合でも）明示的にELSE句を書く癖をつけましょう。その方が、コード上でNULLが生成されることが明らかになりますし、将来的に修正が発生した場合にもミスを減らせます。
1.既存のコード体系を新しい体系に変換して集計する
非定型的な集計を行なう業務では、既存のコード体系を分析用のコード体系に変換して、その新体系の単位で集計したい、という要件が持ち込まれることがあります。
例えば、県コードは、「1：北海道、2：青森、...47：沖縄」というように振られていますが、これを東北、関東、九州といった地方単位にまとめ、その単位で人口を集計したい場合です。具体的には、次に示すPopTbl の内容を集計し、結果を求めるような場合です。
■集計元の表PopTbl ■集計結果
pref name population 地方名 人口
（県名） (人口）
徳島 100 四国 650
香川 200 九州 600
愛媛 150 その他 450
高知 200
福岡 300
佐賀 100
長崎 200
東京 400
群馬 50
こんなとき、皆さんならどうしますか？「地方コード」という列を持－）ビューを定義する、というのも・つの方法です。しかしそれだと、集言十に使いたいコード体系の数だけ列を追加しなければなりませんし、動的な変更も困難です。
CASE式を使うと次のような・つのSQLで取出しが‘げ能です。ここでは分かりやすくするために、コードの代わりに県名（pref_name）をGROUP BYのキーに使います。
/* 県コードを地方コードに再分類する */
SELECT CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE 'その他' END AS '地方名',
SUM(population) AS '人口'
FROM PopTbl
GROUP BY CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE 'その他' END ;
豪快にGROUP BY句にSELECT句のCASE式をコピーしてあげるのがポイントです。単純に「GROUP BY pref_name 」と変換前の列を指定すると、正しい結果が得られないので注意してください（構文エラーにはならないので、見過ごされがちです）。
また、同様の考え方で、数値を適当な階級体系に振り分けて集計することも可能ですの例えば、人口階級（pop_class）ごとの都道府県の数を調べたい場合は、次のようなSQLになります。
/* 人口階級ごとに都道府県を分類する */
SELECT CASE WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END AS pop_class,
COUNT(*) AS cnt
FROM PopTbl
GROUP BY CASE WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END;
pop_class cnt
---------- ---
01 1
02 3
03 3
04 2
このトリックは大変便利なのですが、SELECT句とGROUP BY句の二箇所に同じCASE式を書かねばならないのが少し面倒です。後に修正が発生したときも、片方だけ直してもう一方の修正を忘れてしまう、というミスが起きやすくなります。
そこで、次のような書き方ができたら便利だと思わないでしょうか？
/* 地方単位にコードを再分類する その２：CASE 式を一箇所にまとめる */
SELECT CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE 'その他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY district;
そう、SELECT句でつけた列の別名「district」をGROUP BY句で使っているわけです。ただし厳密に見ると、この書き方は標準SQL違反です。GROUP BY句はSELECT句よりも先に実行されるため、本来はSELECT句で付けた別名をGROUP BY句で参照することは許されないからです。そして事実、Oracle、DB2、SQLServerではこの書式はエラーとなります。
ところが、中にはこのSQL文を通すDBもあるのです。PostgreSQLやMySQLでは、上のクエリを問題なく実行できます。これは、SELECT句のリストを先に走査して、列の計算を事前に行っているからです。標準違反の書き方なので、積極的に勧めはしませんが、コードが非常に簡潔で，涜みやすく書けるのが魅力ではあります。
2.異なる条件の集計を1つのSQLで行なう
異なる条件の集計は、CASE式の使い方として有名なものの一つです。例えば、先の県別人口を保持するテーブルに、性別列を付け加えたテーブルから、男女別・県別の人数の合計を求める、というケースを考えます。具体的には、次に示す表の内容を集計し、次項の表の結果を求めるような場合です。
■集計元の表PopTbl2 ■集計結果
pref_name sex population 県名 男 女
（県名） （性別） （人口）
愛媛 1 100 愛媛 100 50
愛媛 2 50 香川 100 100
香川 1 100 高知 100 100
香川 2 100 ⇒ 佐賀 20 80
高知 1 100 長崎 125 125
高知 2 100 東京 250 150
佐賀 1 20 徳島 60 40
佐賀 2 80 福岡 100 200
長崎 1 125
長崎 2 125
東京 1 250
東京 2 150
徳島 1 60
徳島 2 40
福岡 1 100
福岡 2 200
普通は次のように、WHERE句でそれぞれ異なる条件を記述して、2回SQLを発行します。
■サンブル3
/* 男性の人口 */
SELECT pref_name,
SUM(population) AS '男'
FROM PopTbl2
WHERE sex = '1'
GROUP BY pref_name
/* 女性の人口 */
SELECT pref_name,
SUM(population) AS '女'
FROM PopTbl2
WHERE sex = '2'
GROUP BY pref_name
あとはこれをホスト言語やアプリケーション側で列に展開するわけです。UNIONを使えば1つのSQLにできますが、コストは減りませんし、SQLも無駄に長くなります。
一方、CASE式を使えば、次のような1つのSQLで済みます。
/* 異なる条件の集計を1 つのSQL で行なう */
SELECT pref_name,
/* 男性の人口 */
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
/* 女性の人口 */
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
FROM PopTbl2
GROUP BY pref_name;
性別が男性’1’のレコードと女性’2’の人口列を、それぞれ合計しているわけです。いわば「行持ち」のデータから列持ち」に水平展開しているのです。集約関数であれば、SUMに限らずCOUNTでもAVGでも同様に使えます。
このトリックの重宝するところは、SQLの結果を二次元表の形に整形できることです。単純にGROUP BYで集約しただけだと、その後、ホスト言語やExcelなどのアプリケーション上でクロス表の形に整形しなければなりません。しかし、上の結果を見ると、表側が県名、表頭が性別という、既にクロス表の形式で結果が出力されることが分かります。これは集計表を作るときに非常に便利な機能です。この技をスローガン的に表現するならば、
WHERE句で条件分岐させるのは素人のやること。プロはSELECT 句で分岐させる。
ということです。使い勝手の良い技なので、大いに利用してください。
3.CHECK制約で複数の列の条件関係を定義する
実は、というほどでもないのですが、CASE式はCHECK制約と非常に相性が良いのです。あまりCHECK制約を使わないDBエンジニアも多いかもしれませんが、CASE式と組み合わせたときの表現力の強さを知れば、きっとすぐに利用したくなるでしょう。
例えば、ここに「女性社員の給料は20万以下」という給与体系を持つ会社があるとします。この言語道断な会社の人事テーブルにおいて、この条件をCHECK制約で表現したのが次のSQLです。
/* CHECK 制約で複数の列の条件関係を定義する */
/* 条件法 */
CONSTRAINT check_salary CHECK
( CASE WHEN sex = '2'
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1 )
CASE式を人れ予にして、「社員の性別が女性ならば、給料は20万円以下である」と
いう命題を表現しています。これは命題論理で条件法（conditional）と呼ばれる論理式
で、形式的に書けば「P→Q」となります。
ここでーつ、重要なことを理解してください。それは、条件法と論理積（logical conjunction )との違いです。論理積とは「PかつQ」を意味する論理式で形式的には「P ∧ Q」と書きます。CHECK制約で表現すると次のようになります。
CONSTRAINT check_salary CHECK
(sex = '2' AND salary<=200000)
この2つの制約は、もちろん異なる動作をします。では、一体どのように異なるのでしょうか？以下に解答と解説を挙げますが、先へ進む前にちょりと考えてみてください。
解答
論理積のCHECK制約を付けると、この会社は男性を雇用できなくなる。条件法であれば、男性も働ける。
解説
論理積「P∧Q」を満たす場合は、命題Pと命題Qが共に真か、どちらかが真でもう一方が不明である場合です。つまりこの会社で働けるのは「女性であり、かつ、給料が20万円以下」の社員か、性別または給料の値が不明の社員の場合です（どちらかの条件が偽になるなら、もう片方の条件が不明な人でも働けません）。
一方、条件法「PならばQ」を満たす場合は、PとQが共に真の場合と、Pが偽または不明な全ての場合です。ようするに、「女性である」という前提条件が満たされなかった場合は、給料についての制約は一切考慮されないのです。
CREATE TABLE TestSal
(sex CHAR(1) ,
salary INTEGER,
CONSTRAINT check_salary CHECK
( CASE WHEN sex = '2'
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1 ));
INSERT INTO TestSal VALUES(1, 200000);
INSERT INTO TestSal VALUES(1, 300000);
INSERT INTO TestSal VALUES(1, NULL);
INSERT INTO TestSal VALUES(2, 200000);
INSERT INTO TestSal VALUES(2, 300000); --error
INSERT INTO TestSal VALUES(2, NULL); --error
INSERT INTO TestSal VALUES(1, 300000);
(1 行処理されました)
(1 行処理されました)
(1 行処理されました)
(1 行処理されました)
メッセージ 547、レベル 16、状態 0、行 14
INSERT ステートメントは CHECK 制約 "check_salary" と競合しています。競合が発生したのは、データベース "test_sinanDB"、テーブル "dbo.TestSal" です。
ステートメントは終了されました。
メッセージ 547、レベル 16、状態 0、行 15
INSERT ステートメントは CHECK 制約 "check_salary" と競合しています。競合が発生したのは、データベース "test_sinanDB"、テーブル "dbo.TestSal" です。
ステートメントは終了されました。
(1 行処理されました)
CREATE TABLE TestSal
(sex CHAR(1) ,
salary INTEGER,
CONSTRAINT check_salary CHECK
(sex = '2' AND salary<=200000));
INSERT INTO TestSal VALUES(1, 200000); --error
INSERT INTO TestSal VALUES(1, 300000); --error
INSERT INTO TestSal VALUES(1, NULL); --error
INSERT INTO TestSal VALUES(2, 200000);
INSERT INTO TestSal VALUES(2, 300000); --error
INSERT INTO TestSal VALUES(2, NULL);
INSERT INTO TestSal VALUES(1, 300000); --error
4.複数の列から最大値を選択する
入れ子の CASE 式の使い方を、もう少し練習しておきましょう。次のような、三つの整数列からなる簡単なサンプル・テーブルを考えます。
今回求めるのは、同じ行内の複数の列の最大値です。例えば、x と y について見れば、一行目は 1 < 2 なので 2、四行目は、5 > 4 なので 5、という具合です。Oracle には、これを求めるための GREATEST 関数が存在するので、GREATEST(x, y) とするだけでいいのですが、より一般的にどんな DB でも動作する方法を求めましょう。
比較するのが2列だけなら、朝飯前です。
SELECT CASE WHEN x < y THEN y
ELSE x END AS greatest
FROM Sample;
結果：
greatest
--------
2
2
3
5
読んで字の如く、x が y より小さければ、y を返し、それ以外であれば x を返す、という分岐を表現しています。三行目のように、x = y のときは、どちらを選んでもかまわないので、x を選ぶことにしています。 では次にこれを拡張して、x, y, z の3列から最大値を選択するようにコードを修正しましょう。いわば、GREATEST 関数の一般化です。ここで、入れ子の CASE 式が活躍します。
SELECT CASE WHEN CASE WHEN x < y THEN y
ELSE x END < z THEN z
ELSE CASE WHEN x < y THEN y
ELSE x END END AS greatest
FROM Sample;
結果：
greatest
--------
3
2
3
5
内側の CASE 式は、先ほどの2列バージョンのものと同じです。これで x と y の最大値が求められたわけですから、今度は外側でそれを z と比較する分岐を記述しているわけです。基本的には、4列以上に拡張する場合も同じ要領ですが、コードはかなり読みにくいものになっていきいます。また、反対に最小値を求めたい場合は、不等号の向きを逆にするだけで OK です。
5.条件を分岐させたUPDATE
数値型の列に対して、現在の値を判定対象としてを別の値へ変えたい、というケースを考えます。問題は、そのときの UPDATE の条件が複数に分岐する場合です。例えば、次のような条件です。
１．現在の給料が30万以上の社員は、10%の減給とする
２．現在の給料が25万以上28万未満の社員は、20%の昇給とする
単純に考えると、次のように UPDATE文を2回実行すればよいように思えますが、これは正しくありません。
--条件１．
UPDATE Personnel
SET salary = salary * 0.9
WHERE salary >= 300000;
--条件２．
UPDATE Personnel
SET salary = salary * 1.20
WHERE salary >= 250000 AND salary < 280000;
というのも、例えば、現在の給料が30万円の社員の場合、当然１．の UPDATE によって給料は27万へと減ります。しかしそれで終わりではなく、２．の UPDATE によって32万4000円に増えるのです。減給と見えた人事部の仕打ちは、実は4000円の昇給だったのです！
もちろん、こんな結果は人事部の意図したところではありません。この社員はきっちり27万円に減給せねばなりません。問題は、最初に実行された UPDATE によって、「現在の給料」が変わってしまい、正しい条件判定ができないことにあります。実行する SQL の順番を逆にしても、例えば現在の給料が27万円の社員の場合に同じ問題が発生します。鬼の人事部長の意図を正確に反映する SQL は、次のように CASE式を使って書く必要があります。
UPDATE Personnel
SET salary = CASE WHEN salary >= 300000
THEN salary * 0.9
WHEN salary >= 250000 AND salary < 280000
THEN salary * 1.20
ELSE salary END;
このSQLは正しいうえに一度の実行で済むので速度まで速くなります。これなら人事部長も納得でしょう。
なお、最後の行の「ELSE salary」は非常に重要ですので、必ず書いてください。これがないと、１．と２．のどちらの条件にも該当しない社員の給料はNULLになってしまいます。これは、CASE式に明示的なELSE句がない場合、データベースがデフォルトでNULLを挿入するからです。CASE式を使うときは常に明示的にELSE句を書く（たとえNULLでかまわない場合でも！）癖をつけましょう。
このトリックは応用範囲が広く、これを使えば主キーの値を入れ替えるという荒技も簡単に実現できます。普通、a と b という主キー値を入れ替えるためには、ワーク用の値へ一度どちらかを退避させるか、遅延制約を使わねばなりません。前者の方法など3回の UPDATE が必要になります。しかし、CASE式を使えば一つの SQL で実現できます。
SomeTable SomeTable
主キー 列１ 列２ 主キー 列１ 列２
(p_key) (col_1) (col_2) (p_key) (col_1) (col_2)
a 1 あ ⇔ a 2 い
b 2 い b 1 あ
c 3 う c 3 う
上のようなテーブルについて、主キー a と b を入れ替えるには、次のように書きます。
UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a'
THEN 'b'
WHEN p_key = 'b'
THEN 'a'
ELSE p_key END
WHERE p_key IN ('a', 'b');
一読して分かるように「 a なら b へ、b なら a へ」という条件分岐させた UPDATE を行なっています。主キーだけでなく、もちろんユニークキーの入れ替えも同様に可能です。ポイントは先ほどの昇給・減給の例題のときと同じです。すなわち、CASE式の分岐による更新は「一気に」行なわれるので、主キーの重複によるエラーを回避できるのです。
ただし、このような入れ替えをする必要が生じるということは、テーブル設計にどこか間違いがある可能性が高いので、まずは設計を見直して、必要なければ制約を外してください。
6．テーブル同士のマッチング
CASE式は DECODE関数と違って式を評価できます。それはつまり、CASE式の中で BETWEEN、LIKE、IS NULL といった便利な述語群を使うことができるということです。中でも IN と EXISTS はサブクエリを作れるため、非常に強力な表現力を持ちます。CASE式の中でこの二つを使うことで、サブクエリを SELECT句で書くことができます。
さて、CourseMasterと OpenCoursesを course_id列でマッチングすることを考えます。すると、OpenCoursesとマッチするキーを持つ CourseMasterのレコードに「○」、マッチしないレコードに「×」というラベルを貼る SQL は次のように書けます。
CourseMaster OpenCourses
course_id course_name month course_id
1 経理入門 200706 1
2 財務知識 200706 3
3 簿記検定 200706 4
4 税理士 200707 4
200708 2
200708 4
course_name 200706 200707 200708
経理入門 ○ × ×
財務知識 × × ○
簿記検定 ○ × ×
税理士 ○ ○ ○
/* テーブルのマッチング：IN 述語の利用 */
SELECT CM.course_name,
CASE WHEN CM.course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200706) THEN '○'
ELSE '×' END AS "200706",
CASE WHEN CM.course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200707) THEN '○'
ELSE '×' END AS "200707",
CASE WHEN CM.course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200708) THEN '○'
ELSE '×' END AS "200708"
FROM CourseMaster CM;
/* テーブルのマッチング：EXISTS 述語の利用 */
SELECT CM.course_name,
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200706
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "200706",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200707
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "200707",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200708
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "200708"
FROM CourseMaster CM;
IN と EXISTS どちらを使っても、結果は同じになります。同様に NOT IN と NOT EXISTS を使って「マッチしない」という条件を書くこともできますが、その場合は参照される側のテーブルに NULL が存在するか否かで両者の動作が異なることに注意が必要です。この問題については「3値論理」を参照してください。
7．CASE式の中で集約関数を使う
これはちょっと高度な使い方です。一見すると文法エラーに見えますが、そうではありません。例として、次のような学生のメンバーとクラブのテーブルを考えます。主キーは { 学生ID, クラブID } です。多対多の関係を扱うための関連エンティティの構造です。
std_id club_id club_name main_club_flg std_id main_club
学生ID クラブID クラブ名 主なクラブ
100 1 野球 Y 100 1
100 2 吹奏楽 N 200 3
200 2 吹奏楽 N ⇒ 300 4
200 3 バドミントン Y 400 5
200 4 サッカー N 500 6
300 4 サッカー N
400 5 水泳 N
500 6 囲碁 N
クラブを複数持っている学生 (100, 200) もいれば、一つしか持っていない学生 (300,400,500) もいます。複数持っている学生については、主なクラブがどれかを示すフラグ列に Y または N の値が入ります。一つだけのクラブを持つ学生の場合は、N が入ります。
さて、このテーブルから、次のような条件でクエリを発行します。
１．クラブを一つだけ持つ学生については、そのクラブIDを取得する
２．クラブを複数持つ学生については、主なクラブのIDを取得する
単純に考えれば、次のような二つの条件に対応するクエリを発行すればよいと思われます。「クラブを一つだけ持つか、複数持つか」は、集計結果に対する条件なので HAVING句を使います。
/*--条件１．クラブを一つだけ持つ学生については、そのクラブIDを取得する */
SELECT std_id, MAX(club_id) AS "main_club"
FROM StudentClub
GROUP BY std_id
HAVING COUNT(*) = 1;
結果１：
std_id main_club
300 4
400 5
500 6
/*--条件１．クラブを複数持つ学生については、主なクラブのIDを取得する */
SELECT std_id, club_id
FROM StudentClub
WHERE main_club_flg = 'Y'
AND std_id IN ( SELECT std_id
FROM StudentClub
GROUP BY std_id
HAVING COUNT(*) > 1);
結果２：
std_id club_id
100 1
200 3
/* CASE 式の中で集約関数を使う(p.23) */
SELECT std_id,
CASE WHEN COUNT(*) = 1 /* 一つのクラブに専念する学生の場合 */
THEN MAX(club_id)
ELSE MAX(CASE WHEN main_club_flg = 'Y'
THEN club_id
ELSE NULL END)
END AS main_club
FROM StudentClub
GROUP BY std_id;
結果：
std_id main_club
100 1
200 3
300 4
400 5
500 6
CASE 式の中に集約関数を書いてさらにその中に CASE 式を書くという、眩暈のしそうな入れ子構造ですが、要するにやりたかったことは、「特技を一つだけ持つのか、複数持つのか」という条件分岐を「CASE WHEN COUNT(*) = 1 …… ELSE ……」という CASE 式で実現することです。これはちょっと革命的な書き方です。なぜなら、私たちは SQL 入門の手ほどきを受けるとき、集計結果に対する条件は HAVING 句を使って設定すると習いますが、CASE 式を使えば SELECT 句でも同様の分岐が書けるからです。この技をスローガン的に表現するならば、
HAVING 句で条件分岐させるのは素人のやること。プロは（以下略）
となります。この例題からも分かるように、CASE 式は SELECT 句で集約関数の中にも外にも書くことができます。この自由度の高さが CASE 式の大きな魅力です。
8．やってはいけない間違い
CASE 式を使うとき、絶対にやってはいけない、しかし初心者がやってしまいがちな間違いを紹介しておきましょう。それは、やはりと言うべきでしょうか、NULL が絡むときの問題です。
次の CASE 式を見てください。
CASE col_1
WHEN 1 THEN '○'
WHEN NULL THEN '×'
ELSE NULL END
意図していることは明らかです。col_1 が 1 ならば「○」を、NULL ならば「×」を返したいわけです。確かに、col_1 が 1 の場合は、問題なく「○」が返ります。しかしこの CASE 式が「×」に評価されることは、決してありません。二番目の WHEN 句が常に unknown になってしまうからです。結局のところ、この WHEN 句が、「WHEN col_1 = NULL」の簡略版に過ぎないことを忘れないでください。正しく動作させるためには、次のように記述します。
CASE WHEN col_1 = 1 THEN '○'
WHEN col_1 IS NULL THEN '×'
ELSE NULL END
もっとも、一番良い方針が、テーブル設計の段階で col_1 に NOT NULL 制約をつけておくことであることは、言うまでもありません。そうすれば、WHEN 句の条件を記述するときに無用の間違いを回避することができます。
おわりに
最後に、少し細かい話をします。CASE「式」であって CASE「文」ではないので、間違えないようにしてください。SELECT「文」や UPDATE「文」のような、一つの実行の単位ではなく、「1 + 1」や「a / b」と同じ式の仲間なので、実行時には評価されて一つの値になります。手続き型言語の CASE文と混同しやすいのですが、別物なので注意してください。SQL はそのような手続きと縁を切ることで成立した言語です。