はじめに
SQLのクラスを教えるとき、最大の課題の一つが、生徒たちがそれまでに手続き型言語から身に付けたことを、一度「頭から追い出す（unlearn）」ことだ。私がそのとき採る一つの方法は、処理を「レコード単位」ではなく、集合という観点から考えるよう強調することである。
――――J.セルコ
SQLの考え方を習得するときに最大の障壁となるのが、私たちの多くが慣れ親しんだ手続き型言語の考え方（ソート、ループ、分岐、代入、等々）です。SQLの本質を理解するには、私たちの心に強固に貼り付いてしまった思考パターンを、一度ベリベリと引き剥がし、更地に戻してやる必要があります。それが、セルコが「unlearn」という言葉に込めたニュアンスです。セルコ自身、Fortranからプログラマとしてのキャリアを開始し、C、Algol、Pascalと手続き型言語を渡り歩いた後にSQLを身に付けた人物だけに、言葉に実感がこもっています。
とはいえ、言うは易し、行うは何とやら。ずっと昔に身につけ、長く使ってきた安定感のある考え方を手放すことは、そう簡単ではありません。この「unlearn」という作業をスムーズに行い、かつ、SQLの集合指向的な考え方を理解するために一番有効な方法は、「HAVING句の使い方を学ぶこと」です。なぜなら、HAVING句はまさにレコードではなく集合レベルで作用する演算子なので、使いこなすためには、否が応でも集合レベルで考えることを要請されるからです。
というわけで、「HAVING句の力」第2回です。今回も、張り切って円を描きましょう。皆さん、ペンとノートの準備はいいですか？
稼働環境
- Oracle
- SQL Server
- DB2
- PostgreSQL
- MySQL（バージョン4.1以上）
対象読者
HAVING句の基本的な使い方を知っている方。「HAVING句の力」の続編にあたるので、未読の方は先にこちらを読むと理解が増すでしょう。
各隊、総員点呼！
突然ですが、あなたは今、消防隊（地球防衛隊でもかまいませんが）の統括を行う責任者に任命されたとします。すると早速、司令部へ出動要請の入電が！
あなたの仕事は、いま現在出動可能な部隊を検索することです。出動可能な条件は、隊のメンバー全員が「待機」状態にあることです。使うテーブルは、次のようなものです。
| member（隊員） | team_id（チームID） | status（状態） |
| ジョー | 1 | 待機 |
| ケン | 1 | 出動中 |
| ミック | 1 | 待機 |
| カレン | 2 | 出動中 |
| キース | 2 | 休暇 |
| ジャン | 3 | 待機 |
| ハート | 3 | 待機 |
| ディック | 3 | 待機 |
| ベス | 4 | 待機 |
| アレン | 5 | 出動中 |
| ロバート | 5 | 休暇 |
| ケーガン | 5 | 待機 |
このサンプル・データにおいて、出動可能な隊は3チームと4チームです。4チームはベス一人しかいませんが、全員揃っていることに違いはありません。これを求めるクエリを考えましょう。
「すべてのメンバーの状態が『待機中』である」という条件は、全称量化文ですから、NOT EXISTSを使って書くことができます。
-- 全称文を述語で表現する SELECT team_id, member FROM Teams T1 WHERE NOT EXISTS (SELECT * FROM Teams T2 WHERE T1.team_id = T2.team_id AND status <> '待機' );
team_id member
------- ----------
3 ジャン
3 ハート
3 ディック
4 ベス
これは、次のような全称量化と存在量化の同値変換を利用しています（「量化って何？」という方は「SQLで数列を扱う」（特にコラム「SQLと量化」）を参照）。
「すべてのメンバーの状態が待機中である」 ＝「待機中ではないメンバーが一人も存在しない」
このクエリは、パフォーマンスにすぐれ、具体的なチームメンバーも表示できるため情報量が多いという利点を持ちます。ですが、二重否定を使うため、直観的には少し分かりにくいクエリです。HAVING句を使うと、次のような簡単な書き方ができます。
-- 全称文を集合で表現する：その1 SELECT team_id FROM Teams GROUP BY team_id HAVING COUNT(*) = SUM(CASE WHEN status = '待機' THEN 1 ELSE 0 END);
結果：
team_id
-------
3
4
これは素直な肯定文ですから、読みやすくコードも簡潔です。では、このクエリがどういう動作をしているのか、詳しく見ていきましょう。まず、定石に従ってGROUP BY句で元のTeams集合を、チーム単位の部分集合に分割します。
欲しい集合は、S3とS4です。では、この二つだけが持っていて、他の集合が持っていない性質とは何でしょう。それは、状態が「待機」の行数と、集合全体の行数が一致する、という性質です。これをCASE式によって表現しているわけです。CASE式は、状態が「待機」なら1を、それ以外なら0を返します。いわばこのCASE式は、ある要素（＝行）が集合に含まれるかどうかを決める関数を表しているのです。こういう関数を特性関数（characteristic function）、または集合を定義するという意味で定義関数と呼びます。
ちなみに、HAVING句の条件は次のように書くことも可能です。
-- 全称文を集合で表現する：その2 SELECT team_id FROM Teams GROUP BY team_id HAVING MAX(status) = '待機' AND MIN(status) = '待機';
このクエリの意味、分かりますか？ ある集合について、その要素の最大値と最小値が一致したなら、実はその集合は一種類の値しか含んでいなかった、ということです。複数の値が含まれていたら、最小値と最大値は絶対にズレるはずですから。極値関数は、引数の列のインデックスを利用できるので、こちらの方がパフォーマンスは良いでしょう（値が3種類しかない今回のケースでは、大した意味はありませんが）。
あるいは集合に対する条件をSELECT句に移して、総員スタンバイか、足りないメンバーがいるかを一覧表示してあげるのも、気が利いています。
-- 総員スタンバイかどうかをチームごとに一覧表示 SELECT team_id, CASE WHEN MAX(status) = '待機' AND MIN(status) = '待機' THEN '総員スタンバイ' ELSE '隊長！ メンバーが足りません' END AS status FROM Teams GROUP BY team_id;
team_id status
------- --------------------------
1 隊長！ メンバーが足りません
2 隊長！ メンバーが足りません
3 総員スタンバイ
4 総員スタンバイ
5 隊長！ メンバーが足りません
ただし、SELECT句へ条件を移した場合、オプティマイザによる最適化を受けられる可能性は低くなるので、HAVING句よりもパフォーマンスは劣るでしょう。この場合、情報量とパフォーマンスはトレードオフの関係になるのです。
