HAVING句で論理演算を行おう

これまで後ろ向きな話が多かったので、気が滅入ってしまったかもしれませんね。本節では、SQLで真理値と論理演算を有効活用するとどんな便利なことができるか、というポジティブな話をします。

SQLの中で論理演算が行われる個所は、大きく3つあります。

• ① WHERE句
• ② HAVING句
• ③ CASE式

まあ要するにAND、OR、NOTの演算子が使える場所、と思っていただいて結構です。このうち、最も頻繁に使われるのは①のWHERE句と②のHAVING句ですが、WHERE句についてはそれほど使い方が難しくないので割愛します。他方、HAVING句での論理演算を使いこなしている人は、プロのDBエンジニアでもそれほど多くありません。

例題の前提

ではさっそく、具体的な例題に即して考えてみましょう。例題は、Joe Celko著『SQLパズル　第2版』（⁠翔泳社）の「パズル17 人材紹介会社」を使います。まず、図5のような単純な構造のテーブルを用意します。

ある人材紹介会社に登録されている就業希望者と、その仕事のスキルの組み合わせを表現したものです。本当は、スキルコードも「01102」とかコードで表すのがよりリアリティがありますが、わかりやすさのためスキルの名前をそのまま使うことにします。このテーブルによれば、たとえば100番の人は会計、在庫整理、製造という3つの分野の技術を持っており、300番の人は製造のみの技術を持つ、ということになります。

さて、この会社にはいつもひっきりなしにクライアント企業から人材についての問い合わせが入ります。みなさん、この会社の対応オペレータになったつもりで、クライアントからの問い合わせ条件に合致する人材がいるかどうか、検索してみてください。

①クライアントAからの問い合わせ

次のような条件であるとします。

• 条件：「製造」スキルを持っている人材

これは簡単ですね。スキルが1つだけであれば、WHERE句で条件を記述するだけでOKです（リスト3⁠）⁠。200番以外の全員が「製造」スキルは持っているため、結果は図6のように4人になります。

SELECT DISTINCT candidate_id
  FROM CandidateSkills
 WHERE skill_code = '製造';

candidate_id
------------
100
300
400
500

②クライアントBからの問い合わせ

次のような条件であるとします。

• 条件：「会計」と「製造」の両方のスキルを持っている人材

今度は、条件がもっと厳しい問い合わせが来ました。しかしラッキーなことに、100番と500番の人が合致します。

この条件を表現しようとすると、もうWHERE句に記述してもダメです（リスト4⁠）⁠。

SELECT DISTINCT candidate_id
  FROM CandidateSkills
 WHERE skill_code = '会計'
  AND skill_code = '製造';

このクエリは、結果を1 行も返しません。理由は、WHERE句の条件は同一行に対して適用されるからです。今、ある人物のスキルは、1行につき1つだけ保持する形になっています。したがって、2つ以上のスキルを問い合わせるのに、WHERE句に記述するのは不適切なのです[3]⁠。

条件が複数行にまたがるとき、それはすなわちHAVING句の出番です。正解はリスト5のように記述します。実行すると、図7のように表示されます。

SELECT candidate_id
  FROM CandidateSkills
 GROUP BY candidate_id
HAVING MAX(CASE WHEN skill_code = '会計' THEN 1 ELSE 0 END)      ┐
     * MAX(CASE WHEN skill_code = '製造' THEN 1 ELSE 0 END) = 1; ┘真理値による論理演算を整数の四則演算で代用している

candidate_id
------------
100
500

このHAVING句が何をやっているかというと、図8のように、会計スキルと製造スキルについて、各登録者がスキルを持っているかどうかによって、0/1のビットフラグを立てているのです。

先ほど、0/1のビット演算は、真理値の論理演算と同等であると述べたのを覚えているでしょうか。この解答は、まさにその特性を利用したものです。会計フラグと製造フラグを乗算しているのは、論理演算の「AND」に相当します。

もしみなさんがお使いのRDBが、真理値をサポートしていれば、ここで0/1の代わりにtrue/false（およびAND/OR）を使うこともできますし、そのほうがぱっと見て論理演算であることがわかりやすいのですが、現在、それが可能なのはPostgreSQLのみです。したがって、汎用的な解答としては、上記のようにビット演算を行うものとなります。いずれはどの実装でも真理値が正式にサポートされることを期待しましょう。

③クライアントC からの問い合わせ

続いて、次のような条件であるとします。

• 条件：「在庫管理」と「製造」の両方のスキルを持っている、または「会計」のスキルを持っている人材

今度は「または」が条件に入ってきました。論理式で書けば次のようになります。

• （⁠「⁠在庫管理」AND「製造⁠」⁠）OR「会計」

これも考え方は②のときと同じです。ORは、ビット演算では足し算で代用します（リスト6⁠）⁠。結果は図9のようになります。

SELECT candidate_id
  FROM CandidateSkills
 GROUP BY candidate_id
HAVING ( MAX(CASE WHEN skill_code = '在庫管理' THEN 1 ELSE 0 END)
       * MAX(CASE WHEN skill_code = '製造' THEN 1 ELSE 0 END))
       + MAX(CASE WHEN skill_code = '会計' THEN 1 ELSE 0 END ) >= 1; （＝でないのが重要!）

candidate_id
------------
100
200
400
500

先ほどと同様、各スキルについて、ビットフラグの一覧表を作ってみましょう（図10⁠）⁠。

今回は条件に「OR」が入っているため、ビット演算に足し算が含まれます。すると、100番の人のように、結果が0/1の範囲を超えて、2以上になる場合があります。これはある意味当然の話で、いかにこの演算が真理値の論理演算を代用するものとはいえ、それは私たちユーザが勝手にそう思っているだけで、DBにとってはただの整数の四則演算だからです。そのため、この演算の結果は、「⁠0か、それ以上か」という形で真偽判定をしなくてはなりません。

整数で真理値を代用した場合の結果判定のルール

• 真：1以上
• 偽：0

そのため、結果判定の際に等号ではなく不等号を使わざるをえないのが気持ち悪いところです（リスト6参照⁠）⁠。

このように、HAVING句で論理演算の考え方を利用すると、非常に便利で幅広い応用が可能になります。そのため、DBを扱う人には積極的に身につけてもらいたい技術なのですが、その障害になるのが、SQLとRDBへの真理値型のサポートの遅れなのです。

終わりに

今回は、いささか厄介な、しかしSQLとデータベースを扱う上で避けては通れないテーマを取り上げました。頭がこんがらがった方もいるかもしれませんので、最後にまとめましょう。

• 標準SQLには真理値型も定義されているが、サポートしているDBは少ない
• その理由は、SQLの真理値がunknownという第3の値まで持つ独特の体系（3値論理）であるため
• NULLとunknownは厳密には異なるのだが、PostgreSQLはこれを混同している
• NULLと空文字は厳密には異なるのだが、Oracleはこれを混同している
• しかたないので、現在のところ、SQLで論理演算をしたいときは、0/1のビットフラグでビット演算をするしかない

最後に、参考資料を記します。

参考資料

ミック『達人に学ぶSQL徹底指南書』

（翔泳社,2008）

「1-3 3値論理とNULL」では、SQLにおける3値論理演算がどのように行われるか、そしてそれがいかにSQLに混乱をもたらすかという点を詳細に解説しました。本稿と併せて読むとよいでしょう。

また、なぜSQLがそもそも真理値に3つ目の値を持ち込むことになったのか、という歴史的な事情を知りたい人は「2-8 神のいない論理」を参照してください。

ミック「3値論理とNULL」

そもそもなぜSQLにunknownという第3の真理値が存在するのか、という理由を知りたい方は、こちらの記事を参照。NULLがSQLの演算に含まれた場合の危険性についても解説しています。

明智重蔵「OracleSQL パズル 9-22」

Joe Celkoの『SQLパズル 第2版』（⁠翔泳社）の「パズル17人材紹介会社」に対して、HAVING句で真理値演算を行う解答が解説されています。本稿を書くうえで参考にさせていただきました。