【SQL】JOIN完全ガイド｜INNER・LEFT・RIGHT・FULL・CROSS・自己結合・ON vs USING・3テーブル結合まで解説

-- 部門テーブル（departments）
-- id | name
--  1 | 営業
--  2 | 開発
--  3 | 人事
--  4 | 経営（従業員なし）

-- 従業員テーブル（employees）
-- id | name         | department_id | salary
--  1 | 田中 太郎    |             1 | 55000
--  2 | 鈴木 花子    |             2 | 70000
--  3 | 佐藤 一郎    |             1 | 48000
--  4 | 高橋 美咲    |             2 | 80000
--  5 | 伊藤 健二    |             3 | 52000
--  6 | 渡辺 さくら  |          NULL | 65000  -- 部門未割当

-- 従業員と所属部門を結合して取得
SELECT
    e.name           AS 氏名,
    d.name           AS 部門,
    e.salary         AS 給与
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
ORDER BY e.id;

-- 結果（部門未割当の「渡辺」と、従業員のいない「経営」はどちらも除外される）:
-- 氏名          | 部門 | 給与
-- -----------+------+-------
-- 田中 太郎  | 営業 | 55000
-- 鈴木 花子  | 開発 | 70000
-- 佐藤 一郎  | 営業 | 48000
-- 高橋 美咲  | 開発 | 80000
-- 伊藤 健二  | 人事 | 52000

-- 従業員を全員取得し、部門情報があれば結合する
SELECT
    e.name           AS 氏名,
    d.name           AS 部門,
    e.salary         AS 給与
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
ORDER BY e.id;

-- 結果（「渡辺」は部門未割当だが LEFT JOIN で残り、部門列は NULL）:
-- 氏名            | 部門   | 給与
-- --------------+--------+-------
-- 田中 太郎      | 営業   | 55000
-- 鈴木 花子      | 開発   | 70000
-- 佐藤 一郎      | 営業   | 48000
-- 高橋 美咲      | 開発   | 80000
-- 伊藤 健二      | 人事   | 52000
-- 渡辺 さくら    | NULL   | 65000  ← 左テーブル(employees)の行が残る

-- 部門が未割当の従業員だけを取得（LEFT JOIN + IS NULL パターン）
SELECT e.name, e.salary
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
WHERE d.id IS NULL;

-- 結果:
-- 氏名            | 給与
-- 渡辺 さくら    | 65000

-- 部門を全件取得し、所属する従業員情報を結合する
SELECT
    d.name           AS 部門,
    e.name           AS 氏名,
    e.salary         AS 給与
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id
ORDER BY d.id, e.id;

-- 結果（従業員のいない「経営」部門が NULL で残る）:
-- 部門   | 氏名            | 給与
-- ------+----------------+-------
-- 営業   | 田中 太郎      | 55000
-- 営業   | 佐藤 一郎      | 48000
-- 開発   | 鈴木 花子      | 70000
-- 開発   | 高橋 美咲      | 80000
-- 人事   | 伊藤 健二      | 52000
-- 経営   | NULL           | NULL   ← 右テーブル(departments)の行が残る

-- 上記の RIGHT JOIN と同じ結果をLEFT JOINで書く
SELECT
    d.name           AS 部門,
    e.name           AS 氏名,
    e.salary         AS 給与
FROM departments AS d           -- テーブルの順番を入れ替える
LEFT JOIN employees AS e ON e.department_id = d.id
ORDER BY d.id, e.id;

-- 従業員と部門の両方を全件取得（一致しない側は NULL）
SELECT
    e.name           AS 氏名,
    d.name           AS 部門
FROM employees AS e
FULL JOIN departments AS d ON e.department_id = d.id
ORDER BY d.id, e.id;

-- 結果:
-- 氏名            | 部門
-- 田中 太郎      | 営業
-- 佐藤 一郎      | 営業
-- 鈴木 花子      | 開発
-- 高橋 美咲      | 開発
-- 伊藤 健二      | 人事
-- 渡辺 さくら    | NULL   ← 部門未割当（employees にしかいない）
-- NULL            | 経営   ← 従業員なし（departments にしかない）

-- MySQL では FULL JOIN の代わりに LEFT JOIN + UNION ALL + RIGHT JOIN を使う
SELECT e.name AS 氏名, d.name AS 部門
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id

UNION ALL

SELECT e.name AS 氏名, d.name AS 部門
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id
WHERE e.id IS NULL;  -- LEFT JOINで既に取得した行の重複を防ぐ

-- サイズ（S/M/L）× カラー（赤/青）の全組み合わせを生成
-- sizes: 3行, colors: 2行 → 結果: 6行
SELECT s.size_name, c.color_name
FROM sizes   AS s
CROSS JOIN colors AS c;

-- 結果:
-- size_name | color_name
-- ----------+-----------
-- S         | 赤
-- S         | 青
-- M         | 赤
-- M         | 青
-- L         | 赤
-- L         | 青

-- 注意: 大きなテーブルに CROSS JOIN すると結果が爆発的に増える
-- 100万行 × 100万行 = 1兆行（メモリ・時間ともに危険）

-- employees テーブルに manager_id 列があるとする
-- manager_id は同じテーブルの id を参照する（NULL = 管理職なし）
SELECT
    e.name         AS 従業員,
    m.name         AS 上司
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
ORDER BY e.id;

-- 結果:
-- 従業員          | 上司
-- 田中 太郎      | NULL         ← 上司なし（管理職）
-- 鈴木 花子      | 田中 太郎
-- 佐藤 一郎      | 田中 太郎
-- 高橋 美咲      | NULL         ← 上司なし（管理職）
-- 伊藤 健二      | 高橋 美咲

-- テーブル構成:
-- orders（注文）: order_id, customer_id, product_id, qty
-- customers（顧客）: customer_id, customer_name
-- products（商品）: product_id, product_name, unit_price

SELECT
    o.order_id,
    c.customer_name  AS 顧客名,
    p.product_name   AS 商品名,
    o.qty            AS 数量,
    o.qty * p.unit_price AS 金額
FROM orders     AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id
INNER JOIN products  AS p ON o.product_id  = p.product_id
ORDER BY o.order_id;

-- ON: 列名が異なっても使える（汎用的）
SELECT e.name, d.name AS 部門
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;

-- USING: 両テーブルで同一列名の場合に短く書ける
-- （employees.department_id と departments.department_id が同名の場合）
SELECT e.name, d.name AS 部門
FROM employees AS e
INNER JOIN departments AS d USING (department_id);

-- USING を使うと SELECT * での重複列が除かれる
-- ON では department_id が両テーブルから2列出る
-- USING では department_id は1列だけ出る

-- JOINのパフォーマンスを確認するには EXPLAIN（実行計画）を使う
EXPLAIN
SELECT e.name, d.name AS 部門
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;

-- type列が "ALL" はフルスキャン（インデックス不使用）→ インデックスを追加
-- type列が "ref" や "eq_ref" は効率よくインデックスが使われている