SQLの集合演算をテーブル結合でやってみる

SQLの集合演算
和集合：UNION / UNION ALL
差集合：EXCEPT
積集合：INTERSECT
関係除算を自力でやる
- 余りのある関係除算
- 厳密な関係除算
参考

集合演算子を使うよりテーブル結合のほうが処理が速いこともあるらしい。

SQLの集合演算

和集合：UNION / UNION ALL
差集合：EXCEPT
積集合：INTERSECT
関係除算：なし

和集合：UNION / UNION ALL

UNIONは重複削除。
UNION ALLは重複も含む。こっちのが処理速度速い。

　
↑元テーブル(classaとclassb）

テーブル結合ではFULL OUTER JOIN。

select * from classa a
full outer join classb b
on a.name = b.name

select name from classa
union
select name from classb

UNIONは縦に結合。
FULL OUTER JOINは横に結合。

差集合：EXCEPT

引き算。classA - classB

select a.name from classa a
left outer join classb b
on a.name = b.name
where
b.name IS NULL --ここがポイント

select name from classa
except
select name from classb

実行結果

積集合：INTERSECT

select a.name from classa a
inner join classb b
on a.name = b.name

select name from classa
intersect
select name from classb

実行結果

関係除算を自力でやる

関係除算とは、あるテーブルから別のテーブルに含まれる全ての値を持つレコードを抽出する操作です。

方法は下記の3つがある。

NOT EXISTSを入れ子にする
HAVING句を使った1対１対応を利用する
割り算を引き算で表現する

元テーブル（classb・classc）

余りのある関係除算

・割り算を引き算で表現する

SELECT * FROM classc c1
where
not exists(
select name from classb
except
select name from classc c2
where
c1.team = c2.team
)

HAVING句を使った1対１対応を利用する

select c.team from classc c
inner join classb b
on c.name = b.name
group by c.team
having count(b.name) = (select count(name) from classb)

実行結果

上記の２つは余りのある関係除算。
イメージは下記。

classC　÷　classＢ　＝　teamA余り（山田）
　　　　　　　+
classC　÷　classB　＝　teamB余りなし

＝2余り（山田）

厳密な関係除算

余りなしの関係除算のこと。

・割り算を引き算で表現する

SELECT * FROM classc c1
where
not exists(
select name from classb
except
select name from classc c2
where
c1.team = c2.team
)
and not exists(
select name from classc c3
where
c1.team = c3.team
except
select name from classb
)
# 「B - C = 0」 ＆ 「C - B = 0」　なら余りなしの厳密な除算になる

HAVING句を使った1対１対応を利用する

select c.team from classc c
left outer join classb b #left outer join
on c.name = b.name
group by c.team
having count(b.name) = (select count(name) from classb) #結合後のb.nameの数と結合前のb.nameの数の比較
and
count(c.name) = (select count(name) from classb) #結合後のc.nameの数と結合前のb.nameの数の比較