結合と集合演算

SQLにはテーブル結合集合演算という、互いに似通って何となく区別しにくい考え方があります。

これらの概念はルールや用途などが異なるのですが、双方とも「複数のデータを統合する」という役割があるので、いまいち区別がつきにくいのです。

端的にいえば、結合はテーブルに対して行う操作であり、集合演算はメインクエリに対して行う操作です。

以降では、まず結合と集合演算、それぞれの概要を示して、最後に双方の違いをまとめようと思います。

 

結合について

冒頭で書いたように、結合(Join)というのはテーブル同士をある条件に基づいてくっつけることです。ただ一口に結合といっても、幾つか種類がありますので以下にそれらの種類を書いていきます。

自然結合(Natural Join)

普通、SQLで結合といえばこの自然結合を指すのではないでしょうか?

この自然結合はただのカテゴリ名称であり、実際には下記の4つの自然結合を利用します。

内部結合(Inner Join)
概要 テーブル同士の指定キーが一致したレコードのみ抽出
結合パターン 論理積(AND)
結合テーブル順序 可換
結果レコード数 他の結合よりも結果レコード数は少ない傾向
ANSI構文 FROM T1 INNER JOIN T2 ON T1.row = T2.row
別構文 FROM T1,T2 WHERE T1.row = T2.row
左外部結合(Left Outer Join)
概要 内部結合の結果+左側だけにあるレコードを抽出
結合パターン 左テーブルを基準とした論理和(OR)
結合テーブル順序 非可換
結果レコード数 定義から、内部結合よりは多くなる
ANSI構文 FROM T1 LEFT OUTER JOIN T2 ON T1.row = T2.row
右外部結合(Right Outer Join)
概要 内部結合の結果+右テーブルだけにあるレコードを抽出
結合パターン 右テーブルを基準とした論理和(OR)
結合テーブル順序 非可換
結果レコード数 定義から、内部結合よりは多くなる
ANSI構文 FROM T1 RIGHT OUTER JOIN T2 ON T1.row = T2.row
完全外部結合(Full Outer Join)
概要 (左外部結合+右外部結合)から重複を取り除いた結果
結合パターン 論理和(OR)
結合テーブル順序 可換
結果レコード数 左右外部結合を合わせた結果なので多い
ANSI構文 FROM T1 FULL OUTER JOIN T2 ON T1.row = T2.row

 

直積結合=交差結合=クロス結合(Cross Join)

呼び方は色々とありますが、指しているのは全て同じ Cross Join です。この結合では、結合する2テーブルの各レコードの全組合せを抽出します。そのため、例えば、K行L列のテーブルとM行N列のテーブルを直積結合すると、K*M行 L+N列のテーブルが構成されます。

行数が K*M となるため、テーブルによっては結果レコード数が膨大になります。(1000レコードと100レコードの直積結合は10万レコードになってしまいます・・・泡)

内部結合のように、テーブル順序は可換です。

構文は次の2パターンあります。いづれも意味的には全く同じですが、パターン2はANSI標準の構文ですし、クロス結合が明示的なので、こちらのほうが親切な書き方でしょう。

//パターン1
SELECT * FROM T1, T2
//パターン2
SELECT * FROM T1 CROSS JOIN T2

 

集合演算について

集合演算とは、クエリ結果を合わせることです。構文は次のとおりです。

query1 [ UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL] ] query2

DBMSの種類によって多少の違いはありますが、大方上記のような構文です。ここで重要なのは、集合演算した結果もクエリになるということです。これは、集合演算自体をいくつでも連結できるということに他なりません。

今さらですが、クエリというと SELECT~ の形をしています。しかしながら、クエリ結果とは、要は値のリストです。SUMやAVGのような集計関数だけを配したクエリのような単なるを返す場合や、1列だけですが複数行あるようないわば列ベクトルの場合、逆に複数列で1行だけのような行ベクトル、複数行複数列という非常に一般的なクエリである行列形式の場合などがありますが、要は値のリストです。そのため、説明を簡潔にするためにクエリを列ベクトルのような値のリストに置換して集合演算を考えます。

行ベクトル・列ベクトル


query1 → (2,7,1,8,2,8,1,8,2,8) query2 → (3,1,4,1,5,9,2,6)


UNION [ALL]

まずは、UNIONの動作を見てみましょう

query1 UNION query2 → (1,2,3,4,5,6,7,8,9)

という綺麗な数字列になりました。このUNION演算は、両者のクエリにあろうがなかろうが、全てのデータを統合します。7はquery1にしかありませんし、9はquery2にしかありません。1や2は両方にありますが、演算結果を見ると全てが含まれていることが分かります。なお、1はそれぞれに2つずつありますが、演算結果では1つになってます。複数個の重複は1つにまとめられるのが、このUNIONの特徴です。

次に、UNION ALLです。

query1 UNION ALL query2 → (1,1,1,1,2,2,2,2,3,4,5,6,7,8,8,8,8,9)

違いは一目瞭然ですが、具体的には、重複をまとめないという点がただのUNIONと異なる点です。そのため、データの種類は全く同じで1~9の数値です。

INTERSECT [ALL]

さて、次はINTERSECTです。早速、結果を見てみましょう。
query1 INTERSECT query2 → (1,2)

UNIONと違ってずいぶんと結果が寂しくなりました。直感でも分かるとおり、これは両方のクエリにあるデータのみを抽出する演算です。そのため、どちらか一方にのみあるデータは除外されます。ちょうど論理演算のANDのような考え方です。

query1 INTERSECT ALL query2 → (1,1,1,1,2,2,2,2)

なお一部のDBMSではINTERSECT ALLをサポートしてないようです。

 

EXCEPT

Oracleなど一部のDBMSではMINUSという演算子になっていますが、これは2つのクエリの差をとる演算です。つまり、query1とquery2の差とは、query1とquery2の重複部分をquery1から除去したものです。結果は次のようになります。
query1 EXCEPT query2 → (7,8)
これも元データから容易にわかる結果だと思います。ただしこのEXCEPT、クエリの順序を変更すると結果が変わってしまうため注意が必要です。では、query1とquery2の順番を逆にしてみます。
query2 EXCEPT query1 → (3,4,5,6,9)

この可換則を満たさないところが、UNIONやINTERSECTと異質なところなのです。まぁ考えてみてもみなくても、 a-b ≠ b-a(a≠bのとき)なので当たり前といえば当たり前ですが・・・

 

一見使いどころが分からない集合演算子ですが、状況によっては強力に機能するので、こんな方法もあったなぁ・・・ 程度に覚えておくと、いつか有用な使い方をする機会が訪れるでしょう。

 

結合と集合演算の類似点と相違点

さて最後に、結合と集合演算の違いなぞをまとめて終わりとします。

類似点

両者の類似点は、「レコード情報などを合わせて統合する」「統合方法に論理和ORや論理積ANDの概念がある」「From句などで使うことが出来る」、このあたりでしょう。

確かに、これでは混乱もするはずです。。。

 

相違点

結合
  • テーブルやクエリに対して実行できる
  • データ構造(列数やデータ型)が異なってもよい ※キーとする列のデータ型は同じじゃなきゃいけない
  • 結合条件としてキーを指定する
集合演算
  • テーブルには使えずクエリにのみ使える
  • データ構造(列数やデータ型)が同じでなければならない
  • 結合条件などにキーを指定しない

 

数学で「ある集合とその元」といえば、元のそれぞれの値こそ異なれど、元の性質は同じでなければならないということを示唆しています。集合演算においては、どんなテーブルからのどんな条件のクエリでもいいけど、集合演算の対象となるレコードは同じデータ構造、つまり同じ集合に属させてよー、ということなのです。

散々書いておいてなんですが、この似て非なる両者の概念。理解への最短距離は実際に使いなれることでしょう。実際に使わないことには、有用さや区別が実感できないと思っています。