集合演算 — UNION / INTERSECT / EXCEPT

本記事で使うデータ — employee と contractor

集合演算（複数の SELECT 結果を、和・積・差の集合として 1 つに合成する演算）は、別々に取り出した結果を縦に積み上げたり、両方に共通する行・片方にしかない行を抜き出したりするための構文です。

UNION（和集合）、UNION ALL（重複を保持した和集合）、INTERSECT（積集合）、EXCEPT（差集合）の 4 つを扱います。

題材は社員テーブル employee（30 行）と、外部委託先テーブル contractor（6 行）です。

employee の中で条件の違う 2 つの結果をつないだり、employee と contractor のように別テーブルから取り出した結果を 1 つにまとめたりして、4 つの集合演算を順に試します。

演習に入る前に、本記事で使う 2 つのテーブル — employee と contractor — の 列定義 と データのサンプル を確認しておきます。

① PRAGMA table_info(employee); と PRAGMA table_info(contractor); で両テーブルの列定義を確認してください。

② SELECT * FROM employee LIMIT 5; と SELECT * FROM contractor LIMIT 5; で先頭 5 行のデータをプレビューしてください。

集合演算の前提 — union 互換であること

4 つの集合演算は、いずれも SELECT ... 演算子 SELECT ... の形で 2 つ以上の SELECT 結果をつなぎます。

つなぐ前提として、上下の SELECT が union 互換（列数が等しく、対応する列の型が両立する）である必要があります。

SELECT name FROM employee と SELECT name FROM contractor は両方とも 1 列の文字列なので互換です。

一方 SELECT name FROM employee と SELECT name, city FROM contractor は列数が違うのでつなげません。

-- union 互換: 両側とも (name, city) の 2 列
SELECT name, city FROM employee WHERE city = 'Tokyo'
UNION
SELECT name, city FROM contractor
ORDER BY name;

-- ORDER BY は全体の最後に 1 つだけ
-- 途中の SELECT ごとの ORDER BY は書けない

UNION と UNION ALL — 重複を消すか残すか

UNION は 2 つの結果を縦に積み上げ、完全に同じ行を 1 つにまとめます（重複排除）。

UNION ALL は重複排除をせず、両方の行をそのまま全部残します。

重複を消す必要がないとき、または「両方に現れた行が何件あるか」を保ちたいときは UNION ALL を使います。

UNION は内部で重複判定のためのソートが入るぶん、UNION ALL のほうが処理が軽くなります。

下の例では employee の中で「Kyoto 在住」の結果と「給与 700 万以上」の結果をつなぎます。

両方の条件を満たす社員は、UNION だと 1 行に、UNION ALL だと 2 行になります。

-- UNION: 重複行は 1 つにまとめられる
SELECT name FROM employee WHERE city = 'Osaka'
UNION
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

-- UNION ALL: 重複は保持される (両方に該当する行は 2 回出る)
SELECT name FROM employee WHERE city = 'Osaka'
UNION ALL
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

「Kyoto 在住の社員と、給与が高い社員を 1 つの名簿にまとめたい。両方に当てはまる人は 1 回だけ載せる」という要件を想定します。（正しく実行できれば解説が表示されます）

① employee から city が Kyoto の行の name を取り出す SELECT を書いてください。

② salary が 7,000,000 以上の行の name を取り出す SELECT を、重複を排除する集合演算子でつないでください。

③ 結果を name の昇順で並べてください（並び順を保証するため）。

「実践 1 と同じ 2 つの条件の結果を、重複を消さずに全件積み上げて、どの名前が両方に登場したかを目視で確認したい」という要件を想定します。

① 実践 1 と同じ 2 つの SELECT（Kyoto 在住 / 給与 7,000,000 以上の name）を、重複を保持する集合演算子 でつないでください。

② 結果を name の昇順で並べてください。両方に登場する名前が連続して 2 行並ぶことを確認してください。

INTERSECT と EXCEPT — 共通行と差分行

• INTERSECT（積集合）は 両方の結果に共通して現れる行だけ を返します。
• EXCEPT（差集合）は 上の結果にはあって下の結果にはない行 を返します。

どちらも重複は自動的に排除されます（UNION と同じ挙動）。

EXCEPT は上下を入れ替えると結果が変わる 非対称な演算 で、A EXCEPT B と B EXCEPT A は別物です。

下の例では employee の中で「Kyoto 在住」と「給与 650 万以上」の name を INTERSECT でつなぎ、両方を満たす社員（共通行）を取り出します。

-- INTERSECT: Osaka 在住で給与も 6000000 以上の社員
SELECT name FROM employee WHERE city = 'Osaka'
INTERSECT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

-- EXCEPT: Osaka 在住だが給与は 6000000 以上ではない社員
SELECT name FROM employee WHERE city = 'Osaka'
EXCEPT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

「Kyoto 在住で、かつ給与が 6,500,000 以上の社員だけを取り出したい」という要件を、集合演算で解きます。

① employee から city が Kyoto の行の name を取り出す SELECT を書いてください。

② salary が 6,500,000 以上の行の name を取り出す SELECT を、共通行だけを返す集合演算子 でつないでください。

③ 結果を name の昇順で並べてください。

「Kyoto 在住の社員のうち、給与 6,500,000 以上ではない人（給与の低めな Kyoto 勢）だけを取り出したい」という要件を想定します。

① employee から city が Kyoto の行の name を取り出す SELECT を書いてください。

② salary が 6,500,000 以上の行の name を取り出す SELECT を、上にあって下にない行を返す集合演算子 でつないでください（上下の順序が結果を左右するので注意してください）。

③ 結果を name の昇順で並べてください。

別テーブルどうしの集合演算 — employee と contractor

集合演算は単一テーブルの中だけでなく、別々のテーブルから取り出した結果どうし でも使えます。

union 互換（列数・型が一致）でありさえすれば、テーブルが違っても構いません。

結合した結果を並べ替えたいときは、いちばん最後の SELECT のあとに ORDER BY を 1 つだけ書きます（結合後の結果全体に効きます）。

社員 employee と外部委託先 contractor の name を INTERSECT でつなぐと、両方に同じ名前で登場する人を洗い出せます。

下の例では name 1 列だけを取り出して INTERSECT でつなぎます。

両テーブルの name に Alice と Bob があるため、共通行として 2 名が返ります。

-- employee と contractor の両方に出てくる名前
SELECT name FROM employee
INTERSECT
SELECT name FROM contractor
ORDER BY name;

-- contractor にいて employee にいない名前 (外部だけ)
SELECT name FROM contractor
EXCEPT
SELECT name FROM employee
ORDER BY name;

「外部委託先 contractor のうち、社員 employee には同じ名前が存在しない人（純粋に外部だけの名前）を一覧したい」という要件を想定します。

① contractor から name を取り出す SELECT を書いてください。

② employee から name を取り出す SELECT を、上にあって下にない行を返す集合演算子 でつないでください。contractor の SELECT を上に置いてください。

③ 結果を name の昇順で並べてください。