結合タイプの詳細解説
結合タイプの詳細解説
コーヒーショップを経営していると想像してください。毎日3つの状況を処理する必要があります：会員で購入履歴のある顧客（両方のテーブルにデータがある）、購入していない会員（左テーブルにデータがあるが右テーブルにはない）、会員登録せずに購入した顧客（右テーブルにデータがあるが左テーブルにはない）。SQLはこれらのシナリオ（およびそれ以上）を正確に処理するためのさまざまなタイプのJOINを提供します。
1. コアコンセプト
6つの結合タイプの概要
| 結合タイプ | 説明 | 返される結果 |
|---|---|---|
| INNER JOIN | 内部結合 | 両方のテーブルで一致する行のみを返す |
| LEFT JOIN | 左外部結合 | 左テーブルのすべての行 + 右テーブルの一致する行（一致しない場合はNULL） |
| RIGHT JOIN | 右外部結合 | 右テーブルのすべての行 + 左テーブルの一致する行（一致しない場合はNULL） |
| FULL OUTER JOIN | 完全外部結合 | 両方のテーブルのすべての行（一致しない場合はNULL） |
| CROSS JOIN | クロス結合 | 両方のテーブルのデカルト積（すべての組み合わせ） |
| SELF JOIN | 自己結合 | テーブルをそれ自身と結合 |
ベン図でJOINを理解する
2つの円AとBを使用して2つのテーブルを表します：
┌───────┐ ┌───────┐
│ A │ │ B │
│ │ │ │
│ ┌───┼───┼───┐ │
│ │ A ∩ B │ │ │
│ └───┼───┼───┘ │
│ │ │ │
└───────┘ └───────┘
INNER JOIN → A ∩ B（積集合）
LEFT JOIN → A（すべてのA + A∩B）
RIGHT JOIN → B（すべてのB + A∩B）
FULL JOIN → A ∪ B（和集合）
CROSS JOIN → A × B（すべての組み合わせ）
INNER JOIN
両方のテーブルで一致する行のみ を保持します。前のレッスンで詳しく説明したため、ここでは繰り返しません。
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
LEFT JOIN
左テーブルのすべての行 を返し、一致するデータがない場合、右テーブルの列は NULL を表示します。左テーブルは FROM の最初のテーブルです。
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
出力（周九 の部門がNULLであることに注意）：
name department
------ ----------
張三 技術部
李四 技術部
王五 営業部
趙六 経理部
銭七 技術部
孫八 営業部
周九 NULL
呉十 経理部
RIGHT JOIN
右テーブルのすべての行 を返し、一致するデータがない場合、左テーブルの列は NULL を表示します。右テーブルは JOIN の後のテーブルです。
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
-- RIGHT JOIN departments d ON ... と同等
SELECT e.name, d.name AS department
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;
FULL OUTER JOIN
両方のテーブルのすべての行 を返し、一致するデータがない場合は NULL を表示します。LEFT JOINとRIGHT JOINの和集合に相当します。
-- 構文（MySQL/PostgreSQL/SQL Serverでサポート）
SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
LEFT JOIN + UNION + RIGHT JOIN を使用してシミュレートできます：
-- LEFT JOIN UNION RIGHT JOINでFULL OUTER JOINをシミュレート
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
また、SQLiteはRIGHT JOINもサポートしていません。SQLiteではこのように実装できます：
-- SQLiteでの同等のFULL OUTER JOIN
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name AS department
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;
💡 ヒント： 実際には、FULL OUTER JOINはあまり使用されません。ほとんどのシナリオはLEFT JOINで処理できるため、最初の選択肢とすべきです。
CROSS JOIN
2つのテーブルの デカルト積 を返します。左テーブルの各行と右テーブルの各行のすべての組み合わせです。結果の行数 = 左テーブルの行数 × 右テーブルの行数。
SELECT e.name, d.name AS department
FROM employees e
CROSS JOIN departments d;
employeesが8行、departmentsが4行の場合、結果は8 × 4 = 32行になります。
SELF JOIN
テーブルを それ自身 と結合します。通常、「階層リレーションシップ」や「同じテーブル内でのペアリング」のクエリに使用されます。例えば、社員とその上司などです。
-- employeesテーブルにmanager_idフィールドがあると仮定
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
e と m）。そうしないと、SQLは2つの参照を区別できません。
JOIN選択決定ツリー
ビジネス要件に直面した場合、以下のロジックに基づいて結合タイプを選択します：
2つのテーブルを結合する必要がある？
├── 一致するデータのみ → INNER JOIN
├── 左テーブルのすべてのデータを保持 → LEFT JOIN
├── 右テーブルのすべてのデータを保持 → RIGHT JOIN
├── 両方のテーブルのすべてのデータを保持 → FULL OUTER JOIN
├── すべての組み合わせが必要 → CROSS JOIN
└── テーブルがそれ自身に関連する → SELF JOIN
2. 基本構文／使い方
LEFT JOINの構文
SELECT カラム名
FROM テーブル_A
LEFT JOIN テーブル_B ON テーブル_A.カラム = テーブル_B.カラム;
LEFT OUTER JOIN と LEFT JOIN は完全に同等です。OUTER キーワードは省略できます。
ON 条件は右テーブルの照合にのみ影響します。左テーブルのすべての行は保持されます。
RIGHT JOINの構文
SELECT カラム名
FROM テーブル_A
RIGHT JOIN テーブル_B ON テーブル_A.カラム = テーブル_B.カラム;
CROSS JOINの構文
SELECT カラム名
FROM テーブル_A
CROSS JOIN テーブル_B;
-- CROSS JOINにはON条件は不要
SELF JOINの構文
SELECT a.カラム, b.カラム
FROM テーブル_A a
JOIN テーブル_B b ON a.カラム = b.カラム;
例：部門に所属していない社員を検索（難易度⭐）
LEFT JOINを使用して、部門に割り当てられていない社員を含むすべての社員を検索します：
SELECT e.name AS employee,
COALESCE(d.name, '未割り当て') AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
出力：
employee department
-------- ----------
張三 技術部
李四 技術部
王五 営業部
趙六 経理部
銭七 技術部
孫八 営業部
周九 未割り当て
呉十 経理部
COALESCE(d.name, '未割り当て') は：d.name がNULLの場合、「未割り当て」と表示します。
例：注文がない商品を検索（難易度⭐⭐）
productsテーブルには8つの商品がありますが、すべての商品に注文があるわけではありません。LEFT JOINを使用して「売上ゼロ」の商品を検索します：
SELECT p.name AS product, p.category, p.price,
COUNT(o.id) AS order_count
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
GROUP BY p.id, p.name, p.category, p.price
HAVING COUNT(o.id) = 0;
出力（データに依存します。注文がない商品がある場合）：
product category price order_count
------- -------- -------- -----------
Mac Mini Computer 4499.00 0
GROUP BY で集計し、最後に HAVING で注文数がゼロの商品をフィルタリングします。
例：社員と上司の自己結合（難易度⭐⭐⭐）
employees テーブルに manager_id フィールドがあると仮定します。各社員とその上司をクエリします：
-- まずmanager_idフィールドとデータを追加
ALTER TABLE employees ADD COLUMN manager_id INTEGER;
UPDATE employees SET manager_id = 5 WHERE id IN (1, 2, 3, 6);
UPDATE employees SET manager_id = NULL WHERE id = 5;
-- 自己結合クエリ
SELECT e.name AS employee,
COALESCE(m.name, '上司なし') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
出力：
employee manager
-------- --------
張三 銭七
李四 銭七
王五 銭七
趙六 上司なし
銭七 上司なし
孫八 銭七
周九 上司なし
呉十 上司なし
e は「社員としての自分」、m は「上司としての自分」を表します。階層リレーションシップは manager_id = m.id を通じて構築されます。
3. よくある使用ケース
ケース1：部門-社員マトリックスレポートの生成
CROSS JOINを使用して、完全な「すべての部門 × すべての社員」マトリックスを生成します：
SELECT d.name AS department, e.name AS employee
FROM departments d
CROSS JOIN employees e
WHERE e.department_id IS NOT NULL
ORDER BY d.name, e.name;
これは「全社員の勤怠表」を生成するシナリオなどで役立ちます。
ケース2：同じ部門の社員ペアを検索
SELF JOINを使用して、同じ部門のすべての社員ペアを検索します：
SELECT e1.name AS employee1,
e2.name AS employee2,
d.name AS department
FROM employees e1
INNER JOIN employees e2 ON e1.department_id = e2.department_id
AND e1.id < e2.id
INNER JOIN departments d ON e1.department_id = d.id;
出力：
employee1 employee2 department
--------- --------- ----------
張三 李四 技術部
張三 銭七 技術部
李四 銭七 技術部
王五 孫八 営業部
趙六 呉十 経理部
e1.id < e2.id は重複ペア（例：「張三-李四」と「李四-張三」は1つだけ保持）と自己ペア（例：「張三-張三」）を回避します。
❓ よくある質問
質問：LEFT JOINとINNER JOINが同じ結果を生成するのはいつですか？ 回答： 左テーブルにNULLの外部キー値がない場合（つまり、すべての行が右テーブルで一致を見つけた場合）、LEFT JOINとINNER JOINは同じ結果を生成します。ただし、LEFT JOINは右テーブルで一致が見つからなかった左テーブルの行も保持します。
質問：SQLiteがRIGHT JOINとFULL OUTER JOINをサポートしていない場合はどうすればいいですか？ 回答： RIGHT JOINは、テーブルの順序を入れ替えてLEFT JOINを使用することで実装できます。FULL OUTER JOINは、LEFT JOIN + UNION + LEFT JOINでシミュレートできます。実際には、RIGHT JOINはほとんど使用されません。ほとんどのケースではLEFT JOINで十分です。
質問：CROSS JOINは遅くなることがありますか？ 回答： 2つのテーブルがそれぞれ1000行ある場合、CROSS JOINは100万行の結果を生成します。そのため、非常に慎重に使用し、データ量が管理可能であることを確認してください。CROSS JOINはしばしばWHERE条件と一緒に使用され、実質的にINNER JOINと同等になります。
質問：SELF JOINの実用的な用途は何ですか？ 回答： SELF JOINは通常、階層データの処理（例：組織構造、コメント返信、カテゴリツリー）や「同じグループ内のペア」の検索（例：同じ部門の社員、同じ都市の顧客）に使用されます。「同じテーブル内での行間比較」が必要な場合はいつでも、SELF JOINが役立つ可能性があります。
📖 まとめ
- INNER JOIN は一致する行のみを返し、LEFT JOIN は左テーブルのすべての行を保持し、RIGHT JOIN は右テーブルのすべての行を保持します
- FULL OUTER JOIN は両方のテーブルの和集合を返しますが、SQLiteはサポートしていません。LEFT JOIN + UNIONでシミュレートできます
- CROSS JOIN はデカルト積を返します。大きなデータセットでは注意して使用してください
- SELF JOIN はテーブルをそれ自身と接続し、階層リレーションシップや同グループのペアリングに適しています
- 結合選択の決定ツリー：一致にはINNER、保持にはLEFT/RIGHT、すべてにはFULL、組み合わせにはCROSS
- SQLiteはRIGHT JOINとFULL OUTER JOINをサポートしていませんが、テーブルの順序を入れ替えてUNIONと組み合わせることで実現できます
📝 演習
演習1（⭐）：すべての部門とその社員数を表示するクエリを作成してください。社員がいない部門も含めてください。社員数の降順で並べ替えてください。
演習2（⭐⭐）：「注文をしたことがない顧客」をすべて検索するクエリを作成してください。customers テーブルが必要ですが、orders テーブルの customer_name フィールドを使用してシミュレートできます。orders テーブルにのみ存在する顧客と、注文されたことのない商品を検索してください。
演習3（⭐⭐⭐）：SELF JOINを使用して「入社日が隣接する社員ペア」を検索するクエリを作成してください。入社日が最も近い2人の社員を検索します（ヒント：julianday() 関数を使用して日付の差を計算します）。
次のレッスン
👉 09-subquery - サブクエリ：WHERE、FROM、SELECTでのサブクエリの使い方、そしてEXISTS/NOT EXISTSを学びましょう！
