CASE式のススメ（後編）

UPDATEや集計関数との連携によるCASE式の高度な応用

CASE式の中で集約関数を使う

　これは少々高度な使い方です。一見すると文法エラーに見えますが、そうではありません。例として、次のような学生と所属クラブを一覧するテーブルを考えます。主キーは「学生番号、クラブID」です。多対多の関係を扱うための関連エンティティの構造です。

StudentClub

学生番号(std_id)	クラブID(club_id)	クラブ名(club_name)	主なクラブフラグ(main_club_flg)
100	1	野球	Y
100	2	吹奏楽	N
200	2	吹奏楽	N
200	3	バドミントン	Y
200	4	サッカー	N
300	4	サッカー	N
400	5	水泳	N
500	6	囲碁	N

　学生は複数のクラブに所属している場合もあれば（100、200）、1つにしか所属していない場合もあります（300、400、500）。複数のクラブをかけ持ちしている学生については、主なクラブがどれかを示すフラグ列にYまたはNの値が入ります。1つだけのクラブに専念している学生の場合はNが入ります（ここをYにすればずっと簡単なクエリで済むのですが、例題ということでご容赦ください）。

　さて、このテーブルから、次のような条件でクエリを発行します。

1. 1つだけのクラブに所属している学生については、そのクラブIDを取得する
2. 複数のクラブをかけ持ちしている学生については、主なクラブのIDを取得する

　単純に考えれば、次のような2つの条件に対応するクエリを発行すればよいと思われます。「複数のクラブに所属しているか否か」は、集計結果に対する条件なのでHAVING句を使います 。

条件1のSQL文

--条件1：一つのクラブに専念している学生を選択
SELECT std_id, MAX(club_id) AS main_club
FROM StudentClub
GROUP BY std_id
HAVING COUNT(*) = 1;

実行結果1

STD_ID   MAIN_CLUB
------   ----------
300      4
400      5
500      6

条件2のSQL文

--条件2：クラブをかけ持ちしている学生を選択
SELECT std_id, club_id AS main_club
FROM StudentClub
WHERE main_club_flg = 'Y' ;

実行結果2

STD_ID  MAIN_CLUB
------  ----------
100     1
200     3

　確かにこれでも条件を満たす結果が得られますが、例によって複数のSQLが必要となります。CASE式を使えば、次のような1つのSQLで書くことができます。

サンプル4

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;

サンプル4の実行結果

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句で条件分岐させるのは素人のやること。プロはSELECT句で分岐させる。

　となります。この例題からも分かるように、CASE式はSELECT句で集約関数の中にも外にも書くことができます。この自由度の高さがCASE式の大きな魅力です。

陥ってはいけない間違い

　CASE式を使用する際に陥ってはいけない、しかし初心者が陥ってしまいがちな間違いを紹介しておきましょう。それはNULLが絡むときの問題です。

　次の単純CASE式を見てください。

間違った書き方

CASE col_1
WHEN 1    　THEN '○'
WHEN NULL  THEN '×'
END

　意図していることは明らかです。col_1が1ならば「○」を、NULLならば「×」を返したいわけです。確かに、col_1が1の場合は、問題なく「○」が返ります。しかし、この CASE式が「×」に評価されることは、決してありません。というのは、二番目のWHEN句が常にunknownになってしまうからです。結局のところ、このWHEN句が、WHEN col_1 = NULLの簡略版に過ぎないことを忘れないでください。 正しく動作させるためには、次のように記述します。

サンプル5

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 + 1」や「a / b」と同じ式の仲間なので、実行時には評価されて1つの値になります。手続き型言語のCASE文と混同しやすいのですが、別物なので注意が必要です。

　それでは、本稿を読んで、現場でCASE式を使ってみるかという気になってもらえたら幸いです。理屈を理解したら、後はひたすら書いて書いて書きまくることだけが上達の道です。健闘を祈ります。