INTERSECTとEXCEPT

UNION (和集合) を勉強したついでに、INTERSECT (積集合) と EXCEPT (差集合) についても学んでいきます。

これら2つは UNION と同様に、2つのテーブルから1つのテーブルを出力します。2つのテーブルは列数と各列の型を揃える必要があります。

INTERSECT

INTERSECT は、2つのテーブルに含まれる行を取得します。

次のような単一の列を持つ2つのテーブルA、Bがあるとします。

このとき、2つのテーブルに含まれる行からなるテーブルをCとすると、Cは次のようになります。

実際に INTERSECT を使ってみましょう。

-- A
SELECT i
  FROM (VALUES (2), (4), (6), (8), (10)) AS A(i)

INTERSECT

-- B
SELECT i
  FROM (VALUES (1), (1), (2), (3), (5), (8)) AS B(i)

i
2
8

AとBに共通する2と8が取得されます。

テーブル内に重複行を持つB同士で INTERSECT するとどうなるのでしょうか。実際に試してみましょう。

-- B
SELECT i
  FROM (VALUES (1), (1), (2), (3), (5), (8)) AS B(i)

INTERSECT

-- B
SELECT i
  FROM (VALUES (1), (1), (2), (3), (5), (8)) AS B(i)

結果は次の通りです。重複する1は1行のみ出力されています。

i
1
2
3
5
8

これらの結果は、AとBをINNER JOINで結合した結果とほとんど変わりません。キーがない場合などはうまくいきません。

SELECT A.i
  FROM (VALUES (2), (4), (6), (8), (10)) AS A(i)
       INNER JOIN
       (VALUES (1), (1), (2), (3), (5), (8)) AS B(i)
          ON A.i = B.i

EXCEPT

EXCEPTは差集合を表し、2番目のテーブルに含まれる行を取り除いた結果を表示します。

先ほどと同じ、単一の列を持つ2つのテーブルA、Bがあるとします。

このとき、AからBに存在する行を取り除いた結果からなるテーブルをCとすると、Cは次のようになります。

AとBの関係を逆にした場合も、共通行となる 2 と 8 が取り除かれます。ただし、Bに存在する重複行は削除されます。

実際に EXCEPT を使ってみましょう。まずは A EXCEPT B です。

-- A
SELECT i
  FROM (VALUES (2), (4), (6), (8), (10)) AS A(i)

EXCEPT

-- B
SELECT i
  FROM (VALUES (1), (1), (2), (3), (5), (8)) AS B(i)

i
4
6
10

続いて、B EXCEPT A を試してみましょう。

-- B
SELECT i
  FROM (VALUES (1), (1), (2), (3), (5), (8)) AS B(i)

EXCEPT

-- A
SELECT i
  FROM (VALUES (2), (4), (6), (8), (10)) AS A(i)

i
1
3
5

これらの結果は、次のように LEFT OUTER JOIN を使って結合した場合とほとんど同じです。キーがない場合などはうまくいきません。

-- A EXCEPT B
SELECT A.i
  FROM (VALUES (2), (4), (6), (8), (10)) AS A(i)
       LEFT OUTER JOIN
       (VALUES (1), (1), (2), (3), (5), (8)) AS B(i)
          ON A.i = B.i
 WHERE B.i IS NULL

結合条件 A.i = B.i に一致しないBの行については NULL になるので、B.i が NULL の行のみを取得することで共通行を除いた結果が取得できます。