テーブル同士のコンペア―集合の相等性チェック：応用編
前の解法では、テーブルの比較をするのに、事前準備として2つのテーブルの行数を調べておく必要がありました。それほど大きな手間ではありませんが、この準備なしで、いきなり相等性チェックを行えるような改良版を考えましょう。サンプルは、さっきの「tbl_A」と「tbl_B」を使います。
集合論では、一般的に集合の相等性を調べる公式として、以下の2つが知られています。
- (A ⊆ B ) かつ (A ⊇ B) ⇒ (A = B)
- (A ∪ B ) = (A ∩ B) ⇒ (A = B)
1番の方法は、集合の包含関係をもとに相等性を調べる方法です。「AがBを含み、かつBがAを含むなら、両者は等しい」という意味です。これを利用することも可能ですが、少し面倒です（この方法については、後の例題で触れます）。
一方、2番の方法は、集合の和と交差をもとに相等性を調べる公式です。SQLに翻訳すれば、「A UNION B = A INTERSECT Bなら、AとBは互いに等しい」ということです。こちらの方が簡単に書けそうです。
AとBが等しい集合なら、「A UNION B = A = B」でした。そして実は、INTERSECTにも、AとBが等しければ、「A INTERSECT B = A = B」が成り立ちます。そう、UNION以外に冪等性が成立する演算子とは、INTERSECTのことです。
反対に、A ≠ Bであれば、UNIONとINTERSECTの結果は異なります。UNIONの方が絶対に行数が多くなります。最初は異なる集合だったAとBが、次第に接近するアニメーションを想像すると分かりやすいでしょう。
残る問題は、UNIONとINTERSECTの結果をどう比較するかですが、いま常に、
( A INTERSECT B ) ⊆ ( A UNION B )
であることが分かっています。従って、( A UNION B ) EXCEPT ( A INTERSECT B )の結果が空集合かどうかという判定をすればOKです。A = Bのときに空集合になり、A ≠ Bのときに1行以上の行が残ります。
SELECT DISTINCT CASE WHEN COUNT(*) = 0 THEN '等しい' ELSE '異なる' END AS result FROM ((SELECT * FROM tbl_A UNION SELECT * FROM tbl_B) EXCEPT (SELECT * FROM tbl_A INTERSECT SELECT * FROM tbl_B)) TMP;
このクエリもやはり、列名や列数は一切知る必要がなく、NULLを含むテーブルにも使えるという前問と同じ利点を兼ね備えており、さらに、行数を調べる事前準備が不要というすぐれものです。ただし高機能にした分、欠点も発生します。集合演算3回＋DISTINCT1回 の計4回のソートが発生するため、パフォーマンスが落ちます（そう頻繁に実行するタイプのクエリではないでしょうから、このぐらいは許容範囲だと思いますが）。また、INTERSECTとEXCEPTを使うので、現時点ではMySQLでは使えません。利点と欠点を検討して、前問のクエリと使い分けてください。
さて、実際に2つのテーブルが相違することが分かったら、今度は相違した行を具体的に表示しましょう。ファイルに対するdiffコマンドを、テーブルに行うイメージです。これは、排他的和集合を選択すればいいので、次のようになります。
(SELECT * FROM tbl_A EXCEPT SELECT * FROM tbl_B) UNION ALL (SELECT * FROM tbl_B EXCEPT SELECT * FROM tbl_A);
key col_1 col_2 col_3 ----- ------- ------- ------- B 0 7 9 B 0 7 8
A-BとB-Aの間に共通部分は存在しえないので、両者をマージするときは「UNION ALL」でかまいません。このクエリは、片方の集合がもう一方の部分集合である場合にも正しく動きます（その場合は、A-BとB-Aのどちらかが空集合になります）。括弧は演算の順序を指定する極めて重要なものなので、外すと正しい結果が得られません。
それでは、ここで読者に演習問題を出しましょう。実は、前問のUNIONだけを使うクエリも、少し強引な修正を加えることで、事前準備なしで使えるように改善できます。どんな修正を加えればよいか、考えてみてください。
差集合で関係除算を表現する
導入で述べたように、SQLにはまだ除算用の演算子がありません。そのため、除算を行うには自前でクエリを書く必要があります。方法は数多くありますが、代表的なものとしては、
NOT EXISTSを入れ子にするHAVING句を使った1対1対応を利用する- 割り算を引き算で表現する
の3通りが挙げられます。今回は、3番の方法を解説します。
集合代数における引き算とは、差集合演算のことです。以前、「外部結合の使い方」で、差集合を外部結合で書く方法を紹介したときに、この方法を考えてもらう問題を出しましたが、今回はその解答と解説です。
サンプル・データは、社員の技術情報を管理する次の2つのテーブルを使います。
問題は、「EmpSkills」テーブルから、「Skills」テーブルの技術すべてに精通した社員を探すことです。すなわち、答えは相田氏と神崎氏です。平井氏も惜しいのですが、Javaが使えないので選外です。
今回紹介する方法は、HAVINGを使う方法より理解しやすいかもしれません。というのも、見方によっては非常に手続き型に近い発想をするからです。まずは、答えを見てもらいましょう。
SELECT DISTINCT emp FROM EmpSkills ES1 WHERE NOT EXISTS (SELECT skill FROM Skills EXCEPT SELECT skill FROM EmpSkills ES2 WHERE ES1.emp = ES2.emp);
emp ------ 相田 神崎
ポイントは、EXCEPT演算子と相関サブクエリにあります。相関サブクエリは、同じ「EmpSkills」テーブルを関係づけていますが、これは、引き算を社員ごとに行うためです。社員ごとのスキルを、要求されるスキルの集合から引き算して、結果が空集合（φ）なら全部備えていた、残る行があれば足りないスキルがあった、ということです。
例えば相田氏については、
となり、結果が空集合なので合格です。他方、例えば平井氏の場合、
となり、「Java」の行が残るので、結果に含まれません。いわば、処理単位を社員ごとに分割して、割り算よりも簡単な引き算に還元して解いているわけです。「困難は分割せよ」の格言に則った、巧みな解法です。
ところで、このロジックを見て、何か気付いたところはないでしょうか？ 実はこのクエリは、手続き型言語で言うコントロール・ブレイクに似た処理を記述しているのです。試しに、この2つのテーブルがファイルで、1行づつループして処理をすると考えてください。ある社員について、スキルが存在している間は引き算を行い、スキルがなくなったら次の店舗に移り、同じ作業を繰り返します。
相関サブクエリは、よく知られているように、ループの代用としてSQLに導入されました。この解法の方が理解しやすいかもしれない、と言ったのは、こういう意味です。
それでは、ここでも演習問題を出しましょう。上のクエリを「厳密な除算」をするよう修正してください（「厳密な除算」の定義、覚えていますか？）。 今度は過不足なくスキルを満たす社員だけを選択するので、結果は神崎氏ひとりになります。答えはサンプルコードに載せています。
