はじめに
SQLが集合論に立脚する言語であるということは、この連載で一貫して強調してきたテーマの一つです。その特性のゆえに、SQLは「集合指向言語」と呼ばれていますし、実際、集合的な観点から見たときに初めて、その強力さが理解できると私は考えています。しかし現実には、SQLのこの側面は長らく無視されてきました。
その背景には、SQLにも責任の一端があります。というのも、SQLはちょっと前まで、高校で習う程度の基本的な集合演算子すら持っていなかったからです。和（UNION）こそSQL-86からの古参ですが、交差（INTERSECT）と差（EXCEPT）が標準に入ったのはSQL-92ですし、除算（DIVIDE BY）が未だに標準化されていないことは、前にも述べました。だから、SQLが言語として不完全だという批判は、理由のないものではなかったのです。
しかし、現在では標準SQLに基本的な集合演算子が出揃い、それと歩調を合わせて実装も進み、ようやく本格的な応用が可能になってきました。本稿では、集合演算を利用した便利なSQLを紹介し、その考え方を解説することで、これまでとは違った角度からSQLの本質に迫りたいと思います。
稼働環境
- Oracle
- SQL Server
- DB2
- PostgreSQL
- MySQL（バージョン4.1以上 ただし
INTERSECTとEXCEPTを利用するクエリは使えない）
対象読者
集合演算子の基本的な使い方を知っている方。相関サブクエリについて知識があると望ましいので、未読の方は「相関サブクエリで行と行を比較する」を先に読むと良いでしょう。
導入―集合演算に関するいくつかの注意点
SQLの集合演算子は、その名の通り、入力に「集合」を取る演算であり、要するに実装レベルではテーブルやビューを引数に取る演算です。中学や高校で習う集合代数と似ているので、直観的には理解しやすいと思いますが、SQLの場合、いくつか独特な特徴があるので注意が必要です。
注意1：SQLの扱う集合は重複行を許す多重集合のため、それに対応するALLオプションが存在する
通常、集合論で集合と言えば、重複する要素を認めません。だから {1, 1, 2, 3, 3, 3 } という集合は端的に {1, 2, 3 }と同じと見なされます。しかしリレーショナル・データベースにおけるテーブルは、重複行を認める多重集合（multiset, bag）です。
その結果、SQLの集合演算子にも、重複を認めるバージョンと認めないバージョンの2通りが用意されています。通常、UNIONやINTERSECTをそのまま使うと、結果から重複行を排除します。もし重複行を残したい場合は、ALLオプションを付けて、UNION ALLのように記述します。ちょうどSELECT句のDISTINCTオプションと反対の扱いになっています。しかし、なぜか「UNION DISTINCT」のような書き方は許されていません。
この2通りの使い方には、演算の結果以外にもう1つ違いが存在します。それは、集合演算子は重複排除のために暗黙のソートを発生させるが、ALLオプションを付けるとソートが行われないのでパフォーマンスが向上する、という点です。これは効果的なパフォーマンス・チューニングなので、重複を気にしなくてよい場合、または重複が発生しないことが確実な場合には、ALLオプションを付けるとよいでしょう。
注意2：演算の順番に優先順位がある
標準SQLでは、UNIONとEXCEPTに対して、INTERSECTの方が先に実行されるよう定められています。従って、UNIONとINTERSECTを併用するときにUNIONを優先的に実行したい場合は、括弧で明示的に演算の順序を指定せねばなりません。
注意3：DBMSごとに集合演算子の実装状況にバラツキがある
先述の通り、初期のSQLは集合演算の整備を怠ってきました。そのツケとして、DBごとの実装状況も統一性がありません。SQLServerは2005バージョンからINTERSECTとEXCEPTをサポートしましたが、MySQLはまだ両方とも持っていません（中期的な将来に計画されている新機能に含まれています）。また、Oracleのように、EXCEPTをMINUSという別の名前で持っているDBもあります。面倒ですが、Oracleユーザーの方はEXCEPTをすべてMINUSに置き換えて使ってください。
注意4：除算の標準的な定義がない
四則演算のうち、和（UNION）、差（EXCEPT）、積（CROSS JOIN）は標準に入っています。しかし、残る1つ、商（DIVIDE BY）は、諸事情により標準化が遅れています（この「諸事情」は「HAVING句の力」に詳しく書いたのでそちらを参照してください）。そのため、除算をするときは自前でクエリを作る必要があります。
テーブル同士のコンペア――集合の相等性チェック：基本編
さて、それでは集合演算の実践的な応用を見ていきましょう。
DB環境を移行したり、バックアップと最新環境を比較したい場合など、2つのテーブルが等しいか否かを調べたいことがあります。ここで言う「等しい」とは、行数も列数もデータ内容も同じ、つまり「集合として等しい」という意味です。例えば、次の「tbl_A」と「tbl_B」は、テーブル名が違うだけで、集合としては同じです。
こういう等しいテーブル同士が等しいと分かり、等しくないテーブルが等しくないと分かる方法はないでしょうか。たとえるなら、ファイルに対するコンペアを、テーブルに対して行うイメージです。サンプルのように数行程度なら目で確認しても間違いは少ないでしょうが、数百列とか数千万行の規模では無理な相談です。
これを実現する方法は、2通りあります。まずはUNIONだけを使う簡単な方法から見ていきましょう。事前に、「tbl_A」と「tbl_B」の行数は同じであることは確認済みと仮定します（そもそも行数が違ったらその時点で終わりです）。
このサンプルだと、両テーブルとも行数は3です。すると、次のクエリの結果も3であれば、テーブル同士が相等であることが分かります。逆に、結果が3より大きくなれば、相等ではありません。
SELECT COUNT(*) AS row_cnt FROM ( SELECT * FROM tbl_A UNION SELECT * FROM tbl_B ) TMP;
row_cnt
---------
3
なぜそう言えるのでしょうか？ 「導入」の注意1を思い出してください。SQLの集合演算子は、ALLオプションを付けなければ重複行を排除します。それゆえ、「tbl_A」と「tbl_B」が同じなら、重複が排除されてきれいに重なりあうのです。
必然的に、次のような1行だけ相違するテーブルに対しては、結果が「4」になります。相違する行は、重複排除した後も「一体化」せずに残らざるをえないからです。
このクエリは、NULLを含むテーブルにも正しく動作しますし、列数や列名、データ型を一切指定せずに使えるのが便利なところです。UNIONしか使わないのでMySQLでも動きます。もちろん、テーブルの一部だけを比較することもできます。その場合は、比較したい列名を指定し、WHERE句で条件を設定すればOKです。
以上の例からも明らかなように、SQLのUNIONには、任意のテーブルSについてS UNION S = Sとなります。これは、UNIONが非常に重要なある性質を持つことを意味します。それは、数学で冪等性（べきとうせい、idempotency）と呼ばれる性質です。もともと群論などの抽象代数で使われる概念で、いくつか意味がありますが、今回に関係する意味を平たく言うと「二項演算子 * の任意の入力Sについて、S * S = Sが成り立つ」ということです。UNIONはこの意味で冪等です。
また、プログラミングの分野では、この原義を少し拡張して、「繰り返し処理を実行しても、1度だけ実行した場合と結果が同じになる」という意味で使います。例えば身近な例としては、C言語のヘッダーファイルは冪等性を持つよう設計されています。同じファイルを何度インクルードしても、1度インクルードした場合と結果が変わらないからです。同じ意味で、HTTPのGETコマンドも冪等です。同じ要求を繰り返し発行しても安全なようになっているからです。また、この性質は、特にユーザーインターフェイスにおいて重要な役割を果たします。ボタンを何回連打しても1度押したのと同じこと、というのは安全なインターフェイス設計の基本です。
集合演算のUNIONの場合、「S UNION S」を1つの処理単位と見れば、何度実行しても結果が変わりませんから、これも冪等と言えます。従って、三つ以上のテーブルが等しいかどうかを比較することも可能です。
S UNION S UNION S UNION S …… UNION S = S
1つ注意が必要なのは、「UNION ALL」は演算を繰り返すたびに結果が変化するので、冪等性が成立しないことです。似た理由から、重複行を持ったテーブルに対してはUNIONも冪等性を失います。つまり、この美しく強力な性質が成り立つのは、あくまで集合の世界の話であって、多重集合の世界では通用しないのです。皆さん、主キーは大事だよ、ということです。
さて、それでは先に進む前に、ちょっとクイズを出しましょう。実は、集合演算子には、UNION以外にも冪等性を持つものがあります。それは何でしょう？ 次はその演算子も使います。
