INTERSECT、UNION、EXCEPTを理解する

初めに

• Mysql上で動かしています
• INTERSECT、UNION、EXCEPTをまとめて集合演算と呼ぶため、
  本記事でも以降そう呼びます。
• 記載されたクエリはすべて自分の環境で動作確認済みです
• 自主学習の中でやっているため、誤りなどあるかもしれません

クエリだけ見たい人向け

クエリ（クリックして展開）

employee data (large dataset, includes data and test/verification suite)
https://dev.mysql.com/doc/index-other.html

-- INTERSECT(X and Y 重複した値を削除)
select emp_no from salaries where emp_no <=10020 INTERSECT
select emp_no from salaries where emp_no >=10010 and emp_no <=10030;

-- INTERSECT(X and Y 重複した値を保持)
select emp_no from salaries where emp_no <=10020 INTERSECT ALL
select emp_no from salaries where emp_no >=10010 and emp_no <=10030;

-- UNION(X or Y 重複した値を削除)
select emp_no from salaries where emp_no <=10020 UNION
select emp_no from salaries where emp_no >=10010 and emp_no <=10030;

-- UNION ALL(X or Y 重複した値を保持)
select emp_no from salaries where emp_no <=10020 UNION ALL
select emp_no from salaries where emp_no >=10010 and emp_no <=10030;
    
-- EXCEPT(X - Y 重複した値を削除)
select emp_no from salaries where emp_no <=10020 EXCEPT
select emp_no from salaries where emp_no >=10010 and emp_no <=10030;

-- EXCEPT(X - Y 重複した値を削除)
select emp_no from salaries where emp_no <=10020 EXCEPT
select emp_no from salaries where emp_no >=10010 and emp_no <=10030;

集合演算について

まず何か

構造が似ているような複数のテーブルの結果を、縦方向に連結します。

どのような時に使うのか

同じテーブルに対し、異なる条件で取得した複数のテーブルを1つのテーブルにしたいとき

集合演算を行う場合の例

社員マスタから、営業部の年齢上位10名と開発部の年齢上位10名を取得したい
WHEREでは部署ごとに10件絞り出すみたいな事はできない

→営業部の年齢上位10名を取得するクエリXを作成
→開発部の年齢上位10名を取得するクエリYを作成
クエリXとクエリYをUNION ALLで繋げて実行
テーブルXとテーブルYを連結したテーブルが完成！

各集合演算の解説

ALLについて

すべての集合演算は、実行後のテーブルから重複したデータを削除する 機能があります。
UNION ALLのようにALLをつけることによって、上記機能をスキップします。

UNION (X or Y)

テーブルXとテーブルYをそのまま縦に繋げます。

select num from nums where num <= 5 UNION
select num from nums where num >= 4;

select num from nums where num <= 5 UNION ALL
select num from nums where num >= 4;

テーブル情報と実行結果

（集合演演算子より前をテーブルX、後をYとする）

テーブルX	テーブルY	UNION	UNION ALL
1	4	1	1
2	4	2	2
3	5	3	3
4	6	4	4
4	7	5	4
5	8	6	5
		7	4
		8	4
			5
			6
			7
			8
			8

INTERSECT (X and Y)

テーブルXとテーブルYのどちらにも存在するデータだけを抽出します。

select num from nums where num <= 5 INTERSECT
select num from nums where num >= 4;

select num from nums where num <= 5 INTERSECT ALL
select num from nums where num >= 4;

テーブル情報と実行結果

（集合演演算子より前をテーブルX、後をYとする）

テーブルX	テーブルY	INTERSECT	INTERSECT ALL
1	4	4	4
2	4	5	4
3	5		5
4	6
4	7
5	8

EXCEPT (X - Y)

テーブルXから、テーブルYにあるデータを除外します。

select num from nums where num <= 5 EXCEPT
select num from nums where num >= 4;

select num from nums where num <= 5 EXCEPT ALL
select num from nums where num >= 4;

テーブル情報と実行結果

テーブルX	テーブルY	EXPECT	EXPECT ALL
1	5	1	1
2	6	2	2
3	7	3	3
4	8	4	4
4			4
5

最後にサンプルコード

男女別の総支給額トップ5従業員を取得するクエリ

with X as(
    select
        e.emp_no,
        e.first_name,
        e.last_name,
        e.gender,
        sum(s.salary) as salary
    from
        employees as e
    join
        salaries as s
        on e.emp_no = s.emp_no
    group by
        e.emp_no,
        e.first_name,
        e.last_name,
        e.gender
)
(
select * from X where gender = 'M'
order by salary desc
limit 5
)
union all
(
select * from X where gender = 'F'
order by salary desc
limit 5
);