職場で聞かれて自信を持って答えられなかったので、再学習メモ
環境:oracle 11g
準備でとりあえず1~100までの数字を用意します。
with number_table as(
select RowNum as num from dict where RowNum <=100
)
※なんか適当な検証したい時、一括で数字の行を扱う良い方法があれば教えてくださいm(_ _)m
以下の2つの集合を前提に話を勧めます
A = {1,2,3}
select * from number_table where num in (1,2,3)
B = {3,4,5}
select * from number_table where num in (3,4,5)
#和集合
A \cup B
恐らく一番使う機会が多いはずです。
そのためか通常SQLを学習するとき、和集合以外の集合演算子を習わないことが多い気がします。
UNION
みなさんご存知 UNION
A,Bに含まれる要素全てが結果となる。
ただし重複要素は1行にまとめられてしまいます。
select * from number_table where num in (1,2,3)
UNION
select * from number_table where num in (3,4,5)
結果
{1,2,3,4,5}
実際の業務では、分割された複雑なクエリの結果を無理やりつなげるような用途に使われている気がします(笑)。
重複業は1行にまとめられてしまうので、全行出したい場合は以下のUNION ALLを。
ちなみにUNION ALLはこの重複の削除を行わない分UNIONに比べパフォーマンスがいいです。
UNION ALL
A,Bに含まれる要素全てが結果となる。
重複要素も全件出ます。
select * from number_table where num in (1,2,3)
UNION ALL
select * from number_table where num in (3,4,5)
結果
{1,2,3,3,4,5}
差集合
A - B
MySQLではサポートされていないそうなので工夫してください。
http://qiita.com/Hiraku/items/71873bf31e503eb1b4e1
EXCEPT
標準の差集合の演算子はEXCEPT。
ただし今回検証に利用したORACLEではサポートされていなかったので
下記のMINUSのみ検証します。
※oracle以外の人はEXCEPTを利用してください。
MINUS
Aに含まれるがBに含まれない要素が結果となる。
select * from number_table where num in (1,2,3)
MINUS
select * from number_table where num in (3,4,5)
結果
{1,2}
積集合
A \cap B
共通部分を探したい時に使えます。
和集合・差集合の演算子より優先して実行されるため、注意が必要です。
優先順位を変えたい場合は()を使って工夫してください。
※またまたMySQLではサポートされていないそうです。
自己結合などで工夫してください。
http://wiki.minaco.net/index.php?MySQL%2F積集合
INTERSECT
AとB両方に含まれる要素が結果となる。
select * from number_table where num in (1,2,3)
INTERSECT
select * from number_table where num in (3,4,5)
結果
{3}
関係除算
そいうえば割り算もあると本に書いてあったのを思い出して追記。
SQLの演算子としてはサポートされていませんが、
こちらの解説がわかりやすいと思います。
http://www.k4.dion.ne.jp/~type_f/23S_L4/23S_L4DB_09.html
ただし、以下の方法によってSQLでも関係除算を行うことが出来ます
- 1 NOT EXISTSを入れ子にする
- 2 HAVING句を使った一対一対応を利用する
- 3 割り算を引き算で表現する
出展:達人に学ぶSQL徹底指南書
※気が向いたら加筆します。
使い所
これら集合演算子は、出力の形式(データ型、カラム数)が同一の結果に対して使うことが出来ます。
分割された複雑なクエリの結果を無理やりつなげるような用途にも使えますが、
複数テーブル間をまたいだ不正データの調査の時にかなり使えます。
今の職場では複数のバージョンのバッチ処理の結果を別テーブルに比較して、集合演算子を使って、バグの調査などに使っています。