MySQL - JOIN

概要

JOIN (結合) は、複数のテーブルを組み合わせて、関連するデータを一度のクエリで取得するための機能である。

JOIN を使用することで、正規化されたデータベースから効率的にデータを取得し、複数テーブルにまたがる情報を統合できる。
MySQLでは、INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN 等、様々な JOIN の種類が提供されている。

JOIN の構文には、ON 句 (最も柔軟)、USING 句 (同名列の簡潔な記述)、NATURAL JOIN (同名列で自動結合) があり、用途に応じて使い分ける必要がある。
ON 句は異なる列名でも結合可能で、非等価条件にも対応するため、最も汎用性が高い。

MySQL 8.0以降では、ハッシュ結合 (Hash Join) が導入され、等価結合条件がない場合でも高速な結合が可能になった。
結合最適化ヒント (JOIN_ORDER、JOIN_FIXED_ORDER、BKA、BNL等) を使用することにより、オプティマイザーの動作を制御できる。

JOIN のパフォーマンスを最適化するには、適切なインデックスの配置、結合アルゴリズムの理解、EXPLAIN による実行計画の確認が重要である。
詳細な実行計画の確認については、MySQL - EXPLAINのページを参照すること。

NULL の扱いには注意が必要であり、結合条件で NULL = NULL は FALSEとして評価される。
LEFT JOINでは、一致しない行は右側が NULL で埋められる。

JOINの種類

INNER JOIN (内部結合)

INNER JOINは、両方のテーブルで結合条件に一致する行のみを返す。

MySQLでは、JOIN、CROSS JOIN、INNER JOIN は構文的に同等である。(ON 句なしの場合)

 # INNER JOINの基本構文
 SELECT columns
    FROM table1
    INNER JOIN table2 ON table1.column = table2.column;
 
 # 従業員と部署を結合
 SELECT e.name, e.salary, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.id;
 
 # 複数テーブルの結合
 SELECT u.name, o.order_date, p.product_name
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id;
 
 # INNERキーワードは省略可能
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;

等価結合 (=) と 非等価結合 (<、<cdoe>>、!= 等) の両方が使用できる。

 # 等価結合
 SELECT * FROM employees e
    INNER JOIN departments d ON e.department_id = d.id;
 
 # 非等価結合 (給与範囲のマッチング)
 SELECT e.name, s.grade
    FROM employees e
    INNER JOIN salary_grades s ON e.salary BETWEEN s.min_salary AND s.max_salary;
 
 # 複数条件の結合
 SELECT e.name, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.id AND e.hire_date > d.created_at;

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOINは、左側テーブルの全ての行と、右側で一致した行を返す。

一致しない場合は、右側の列が NULL で埋められる。

 # LEFT JOINの基本構文
 SELECT columns
    FROM table1
    LEFT JOIN table2 ON table1.column = table2.column;
 
 # 全ての従業員と、存在する場合は部署情報を取得
 SELECT e.name, d.department_name
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.id;
 # 部署が割り当てられていない従業員も含まれる (department_nameはNULL)
 
 # 注文のないユーザを含めて、全てのユーザを取得
 SELECT u.name, COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name;
 
 # 一致しない行のみを抽出 (WHERE IS NULLを使用)
 SELECT u.name
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE o.id IS NULL;
 # 注文が1件もないユーザのみ取得

LEFT JOIN は、外部キー制約がないテーブルや、一致しないデータを含める場合に有用である。

 # 複数のLEFT JOINを組み合わせる
 SELECT u.name, o.order_date, p.product_name
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    LEFT JOIN order_items oi ON o.id = oi.order_id
    LEFT JOIN products p ON oi.product_id = p.id;
 # 全てのユーザが表示される (注文がない場合はNULL)

LEFT OUTER JOIN と LEFT JOINは同じ意味である。(OUTER キーワードは省略可能)

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOINは、右側テーブルの全ての行と、左側で一致した行を返す。

一致しない場合は、左側の列が NULL で埋められる。

 # RIGHT JOINの基本構文
 SELECT columns
    FROM table1
    RIGHT JOIN table2 ON table1.column = table2.column;
 
 # 全ての部署と、存在する場合は従業員情報を取得
 SELECT e.name, d.department_name
    FROM employees e
    RIGHT JOIN departments d ON e.department_id = d.id;
 # 従業員がいない部署も含まれる (nameはNULL)
 
 # RIGHT JOINはLEFT JOINに書き換え可能
 # 以下の2つは同じ結果を返す
 
 # RIGHT JOIN
 SELECT e.name, d.department_name
    FROM employees e
    RIGHT JOIN departments d ON e.department_id = d.id;
 
 # LEFT JOINに書き換え
 SELECT e.name, d.department_name
    FROM departments d
    LEFT JOIN employees e ON e.department_id = d.id;

RIGHT OUTER JOIN と RIGHT JOIN は同じ意味である。(OUTER キーワードは省略可能)

実務では、LEFT JOINの方が一般的に使用される。
RIGHT JOINは、LEFT JOINに書き換えることで可読性を向上できる場合が多い。

CROSS JOIN (直積)

CROSS JOINは、両方のテーブルの全ての行の組み合わせを返す。(カルテシアン積)

結果行数は、table1の行数 × table2の行数となる。

 # CROSS JOINの基本構文
 SELECT columns
    FROM table1
    CROSS JOIN table2;
 
 # 全ての従業員とすべての部署の組み合わせを取得
 SELECT e.name, d.department_name
    FROM employees e
    CROSS JOIN departments d;
 # employeesが100行、departmentsが10行の場合、結果は1000行
 
 # 暗黙的なCROSS JOIN (カンマ区切り)
 SELECT e.name, d.department_name
    FROM employees e, departments d;
 
 # WHERE句で条件を指定すると、INNER JOINと同じ結果になる
 SELECT e.name, d.department_name
    FROM employees e
    CROSS JOIN departments d
    WHERE e.department_id = d.id;
 # これはINNER JOINと同じ

CROSS JOIN の例としては、以下に示すようなものがある。

 # 日付範囲とユーザのすべての組み合わせを生成
 SELECT d.date, u.name
    FROM date_range d
    CROSS JOIN users u
    ORDER BY d.date, u.name;
 
 # サイズと色のすべての組み合わせを生成 (商品バリエーション)
 SELECT s.size, c.color
    FROM sizes s
    CROSS JOIN colors c;

ただし、CROSS JOINを意図せずに実行すると、大量のデータが生成されるため注意が必要である。

自己結合 (Self Join)

自己結合は、テーブルが自身と結合する方法である。

エイリアスの使用が必須であり、階層構造データや同一テーブル内の行比較に使用される。

 # 自己結合の基本構文 (エイリアス必須)
 SELECT e1.name AS employee, e2.name AS manager
    FROM employees e1
    INNER JOIN employees e2 ON e1.manager_id = e2.id;
 
 # 階層構造データの取得
 SELECT e.name AS employee, m.name AS manager, m.salary AS manager_salary
    FROM employees e
    LEFT JOIN employees m ON e.manager_id = m.id;
 # マネージャーがいない従業員も含まれる
 
 # 同じ部署の従業員を比較 (給与が高い順にペアを作成)
 SELECT e1.name, e1.salary, e2.name, e2.salary
    FROM employees e1
    INNER JOIN employees e2
       ON e1.department_id = e2.department_id AND e1.salary > e2.salary;
 
 # 同じテーブルで異なる条件のデータを比較
 SELECT p1.product_name, p1.price, p2.product_name, p2.price
    FROM products p1
    INNER JOIN products p2
       ON p1.category_id = p2.category_id AND p1.price > p2.price;

自己結合は、再帰的な関係やランキング計算に有用である。

 # 給与が自分より高い従業員の数を数える (ランキング)
 SELECT e1.name, e1.salary, COUNT(e2.id) AS higher_salary_count
    FROM employees e1
    LEFT JOIN employees e2 ON e1.salary < e2.salary
    GROUP BY e1.id, e1.name, e1.salary
    ORDER BY e1.salary DESC;
 
 # 組織階層を表示 (マネージャーとその部下)
 SELECT m.name AS manager, GROUP_CONCAT(e.name) AS subordinates
    FROM employees m
    LEFT JOIN employees e ON e.manager_id = m.id
    WHERE m.manager_id IS NULL
    GROUP BY m.id, m.name;

JOINの構文

ON句

ON句は、最も柔軟なJOIN構文であり、異なる列名でも結合可能で、非等価条件にも対応する。

 # ON句の基本構文
 SELECT columns
    FROM table1
    JOIN table2 ON table1.column = table2.column;
 
 # 異なる列名での結合
 SELECT e.name, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.dept_id = d.id;
 
 # 複数条件の結合
 SELECT e.name, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.id AND e.hire_date > d.created_at;
 
 # 非等価条件の結合
 SELECT e.name, s.grade
    FROM employees e
    INNER JOIN salary_grades s ON e.salary BETWEEN s.min_salary AND s.max_salary;
 
 # OR条件を使用した結合
 SELECT u.name, l.log_message
    FROM users u
    INNER JOIN logs l ON u.id = l.user_id OR u.email = l.email;

ON句では、結合条件以外にもフィルタ条件を追加できる。

 # ON句に追加条件を指定
 SELECT e.name, d.department_name
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.id AND d.location = 'Tokyo';
 # 東京の部署のみ結合、他の従業員も表示される (department_nameはNULL)
 
 # WHERE句に条件を指定 (結果が異なる)
 SELECT e.name, d.department_name
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.id
    WHERE d.location = 'Tokyo';
 # 東京の部署に所属する従業員のみ表示される

LEFT JOINの場合、ON句とWHERE句で条件を指定する位置により、結果が異なる。

• ON句の条件 : 結合時に評価され、一致しない行もNULLで保持される

  フィルタではなく結合条件として機能する。

• WHERE句の条件 : 結合後に評価され、条件に合わない行は除外される

  結果セットのフィルタとして機能する。

USING句

USING句は、両方のテーブルに同名の列がある場合に、簡潔に結合条件を記述できる。

USING句を使用すると、結果セットから重複列が自動的に除外される。

 # USING句の基本構文
 SELECT columns
    FROM table1
    JOIN table2 USING (column_name);
 
 # ON句を使用した場合
 SELECT e.name, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id;
 
 # USING句を使用した場合 (同名列がある場合)
 SELECT e.name, d.department_name
    FROM employees e
    INNER JOIN departments d USING (department_id);
 
 # 複数列での結合
 SELECT *
    FROM orders o
    INNER JOIN order_items oi USING (order_id, product_id);

USING句のメリットと制限を以下に示す。

• メリット

  簡潔な記述、重複列の自動除外

• 制限

  同名列が必要、非等価条件には非対応

 # USING句による重複列の除外
 
 # ON句を使用 (department_idが2回表示される)
 SELECT * FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id;
 # 結果: e.department_id, d.department_id の両方が含まれる
 
 # USING句を使用 (department_idが1回のみ表示される)
 SELECT * FROM employees e
    INNER JOIN departments d USING (department_id);
 # 結果: department_id が1回のみ含まれる

NATURAL JOIN

NATURAL JOINは、同名の列を自動的に結合条件として使用する。

結合条件を明示的に指定する必要がないが、スキーマ変更に脆弱であり、本番環境での使用は推奨されない。

 # NATURAL JOINの基本構文
 SELECT columns
    FROM table1
    NATURAL JOIN table2;
 
 # 同名列で自動的に結合
 SELECT e.name, d.department_name
    FROM employees e
    NATURAL JOIN departments d;
 # department_id等、すべての同名列が自動的に結合条件になる
 
 # NATURAL LEFT JOIN
 SELECT e.name, d.department_name
    FROM employees e
    NATURAL LEFT JOIN departments d;

NATURAL JOINの問題点を以下に示す。

• スキーマ変更に脆弱

  新しい同名列が追加されると、意図しない結合条件が追加される。

• 結合条件が明示されない

  コードの可読性が低下し、メンテナンスが困難になる。

• デバッグが困難

  予期しない結果が生じた場合、原因の特定が難しい。

 # スキーマ変更による問題の例
 
 # 初期状態 : department_idのみが同名列
 SELECT * FROM employees NATURAL JOIN departments;
 # 期待通り、department_idで結合される
 
 # スキーマ変更後 : created_atが両方のテーブルに追加される
 # NATURAL JOINは department_id AND created_at で結合されるようになる
 SELECT * FROM employees NATURAL JOIN departments;
 # 意図しない結合条件になり、結果が変わる

NATURAL JOINは、学習や簡易的なテストには便利であるが、本番環境では明示的なON句またはUSING句を推奨する。

MySQL 8.0以降の新機能

ハッシュ結合 (Hash Join)

MySQL 8.0.18以降では、ハッシュ結合が導入され、等価結合条件がない場合でも高速な結合が可能になった。

MySQL 8.0.20以降では、等価結合条件がない場合でも使用可能である。

 # ハッシュ結合が使用される例
 SELECT *
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 # 適切なインデックスがない場合、ハッシュ結合が選択される
 
 # 非等価結合でのハッシュ結合 (MySQL 8.0.20以降)
 SELECT *
    FROM employees e
    JOIN salary_grades s ON e.salary BETWEEN s.min_salary AND s.max_salary;
 
 # EXPLAIN FORMATで確認
 EXPLAIN FORMAT=TREE
 SELECT *
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 # -> Hash Join が使用される

ハッシュ結合のパフォーマンス特性を以下に示す。

• BNL結合と比較して約1600倍高速化 (シナリオによる)

  大規模結果セットで特に効果的。

• join_buffer_size で制御

  メモリサイズを調整できる。

• メモリ不足時はディスクファイル使用

  一時的にディスクに書き出される。

 # join_buffer_sizeの確認と設定
 SHOW VARIABLES LIKE 'join_buffer_size';
 
 # セッションレベルで変更
 SET SESSION join_buffer_size = 262144;
 
 # グローバルレベルで変更
 SET GLOBAL join_buffer_size = 262144;

結合最適化ヒント

MySQL 8.0以降では、結合最適化ヒントを使用してオプティマイザーの動作を制御できる。

主なヒントを以下に示す。

• JOIN_ORDER

  結合順序を指定する。

• JOIN_FIXED_ORDER

  固定結合順序を強制する。

• BKA / NO_BKA

  Batched Key Access結合を制御する。

• BNL / NO_BNL

  Hash Join (旧Block Nested Loop) を制御する。

 # JOIN_ORDERヒントの使用
 SELECT /*+ JOIN_ORDER(t1, t2, t3) */ *
    FROM t1
    JOIN t2 ON t1.id = t2.t1_id
    JOIN t3 ON t2.id = t3.t2_id;
 
 # JOIN_FIXED_ORDERヒントの使用
 SELECT /*+ JOIN_FIXED_ORDER() */ *
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    JOIN locations l ON d.location_id = l.id;
 # FROM句の順序で結合が実行される
 
 # BKAヒントの使用
 SELECT /*+ BKA(e, d) */ *
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 
 # NO_BNLヒントの使用 (Hash Joinを無効化)
 SELECT /*+ NO_BNL(e, d) */ *
    FROM employees e
    JOIN departments d ON e.department_id = d.id;

Batched Key Access (BKA)

BKAは、インデックスアクセスとジョインバッファを組み合わせた結合アルゴリズムである。

MRR (Multi-Range Read) と連携し、ディスクI/Oを最適化する。

 # BKAの有効化 (optimizer_switch)
 SET optimizer_switch='mrr=on';
 SET optimizer_switch='mrr_cost_based=off';
 SET optimizer_switch='batched_key_access=on';
 
 # BKAが使用される例
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 # d.idにインデックスがあり、BKAが有効な場合
 
 # EXPLAIN FORMATで確認
 EXPLAIN FORMAT=TREE
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 # -> Batched key access が使用される

BKAの特性を以下に示す。

• インデックススキャンを効率化

  ランダムアクセスをバッチ処理する。

• 大量の結合行がある場合に効果的

  ディスクI/Oを削減する。

• MRRとの連携が必須

  optimizer_switchで両方を有効にする。

パフォーマンス最適化

結合アルゴリズム

MySQLは、以下の結合アルゴリズムを使用する。

• Nested Loop Join

  インデックスを利用する際に使用される。

  外部テーブルの各行に対して、内部テーブルをインデックススキャンする。

  インデックスが適切に配置されている場合に高速。

• Hash Join

  インデックスがない場合や大規模結果セットで効率的。

  MySQL 8.0.18以降で使用可能。

  内部テーブルをハッシュテーブル化してルックアップする。

• Block Nested Loop

  古い方法で、Hash Joinに置換された。

  MySQL 8.0.20以降では廃止され、Hash Joinに統一された。

 # Nested Loop Joinが使用される例 (インデックスあり)
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 # d.idにインデックスがある場合
 
 # Hash Joinが使用される例 (インデックスなし)
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 # d.idにインデックスがない場合
 
 # EXPLAIN FORMATで確認
 EXPLAIN FORMAT=TREE
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;

EXPLAINによる実行計画の確認

EXPLAINを使用して、JOINの実行計画を確認できる。

下表に、重要なフィールドを示す。

EXPLAINの主要カラム

カラム	説明	備考
type	アクセスタイプを示す。	ALL (最悪) → index → range → ref → eq_ref → const (最良)
key	使用されたインデックスを示す。	NULLの場合はインデックスが使用されていない。
rows	推定行数を示す。	実際にスキャンされる行数の目安。
Extra	追加情報を示す。	Using join buffer, Using where, Using index 等。

 # 基本的なEXPLAIN
 EXPLAIN
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 
 # EXPLAIN FORMATで詳細表示
 EXPLAIN FORMAT=TREE
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 
 # EXPLAIN ANALYZEで実際の実行時間を確認 (MySQL 8.0.18以降)
 EXPLAIN ANALYZE
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;

詳細なEXPLAINの使用方法については、MySQL - EXPLAINのページを参照すること。

下表に、typeフィールドの値と意味を示す。

typeフィールドの値と意味

値	説明	備考
ALL	フルテーブルスキャン (最も遅い)。	インデックスが使用されていない。
index	インデックス全体をスキャン。	ALLよりはマシだが、依然として遅い。
range	インデックス範囲スキャン。	WHERE句に範囲条件がある場合 (BETWEEN, >, < 等)。
ref	インデックスを使用した等価検索。	複数行が一致する可能性がある。
eq_ref	インデックスを使用した等価検索 (ユニーク)。	最大1行のみが一致する (PRIMARY KEYやUNIQUE KEYの結合)。
const	定数としてアクセス (最も速い)。	PRIMARY KEYやUNIQUE KEYでの単一行検索。

下表に、Extraフィールドの重要な値を示す。

Extraフィールドの重要な値

値	説明	備考
Using join buffer	ジョインバッファが使用されている。	Hash JoinまたはBlock Nested Loopが使用されている。
Using where	WHERE句の条件が結合後に適用されている。
Using index	カバリングインデックスが使用されている。	インデックスのみでクエリを解決。
Using temporary	一時テーブルが使用されている。
Using filesort	ソートが実行されている。

インデックスの最適化

JOINのパフォーマンスを向上させるには、適切なインデックスの配置が重要である。

 # 外部キー列にインデックスを作成
 CREATE INDEX idx_department_id ON employees(department_id);
 
 # 複合インデックスの作成
 CREATE INDEX idx_dept_hire ON employees(department_id, hire_date);
 
 # インデックスの確認
 SHOW INDEX FROM employees;
 
 # インデックスの削除
 DROP INDEX idx_department_id ON employees;

インデックス最適化の指針を以下に示す。

• JOIN条件の列にインデックスを作成

  結合列にインデックスがない場合、Hash Joinが使用される。

• 外部キー列にインデックスを作成

  外部キー制約を持つ列は、インデックスが推奨される。

• 複合インデックスの順序を考慮

  WHERE句とJOIN条件の両方を考慮する。

• カバリングインデックスの活用

  SELECT句の列をすべて含むインデックスを作成する。

詳細なインデックスの使用方法については、MySQL - インデックスのページを参照すること。

NULLの扱い

結合条件でのNULL

結合条件において、NULL = NULL は FALSE として評価される。

 # NULLが含まれる列での結合
 SELECT e.name, m.name AS manager_name
    FROM employees e
    LEFT JOIN employees m ON e.manager_id = m.id;
 # manager_idがNULLの従業員は、manager_nameもNULLになる
 
 # NULL = NULLはFALSE
 SELECT * FROM employees e1
    INNER JOIN employees e2 ON e1.manager_id = e2.manager_id;
 # manager_idがNULLの行は結合されない
 
 # NULLセーフ等価演算子 (<=>)
 SELECT * FROM employees e1
    INNER JOIN employees e2 ON e1.manager_id <=> e2.manager_id;
 # NULLでも結合される (NULL <=> NULL はTRUE)

LEFT JOINでの不一致行

LEFT JOINでは、一致しない行は右側がNULLで埋められる。

 # 一致しない行の検出
 SELECT u.name
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE o.id IS NULL;
 # 注文が1件もないユーザを取得
 
 # NOT EXISTSとの比較 (同じ結果)
 SELECT u.name
    FROM users u
    WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
 
 # NOT INとの比較 (NULLがある場合は注意)
 SELECT u.name
    FROM users u
    WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
 # user_idがNULLを含む場合、NOT INは正しく動作しない

詳細なNOT INの問題については、MySQL - 副問い合わせのページを参照すること。

NULLと集計関数

集計関数は、NULLを無視する (COUNTを除く)。

 # LEFT JOINと集計関数
 SELECT u.name, COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name;
 # 注文がないユーザは order_count = 0
 
 # COUNT(*)とCOUNT(column)の違い
 SELECT u.name,
        COUNT(*) AS total_rows,
        COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name;
 # total_rows は常に1以上、order_count は0の可能性がある
 
 # SUMとNULL
 SELECT u.name, SUM(o.amount) AS total_amount
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name;
 # 注文がないユーザは total_amount = NULL (0ではない)
 
 # COALESCE関数でNULLを0に変換
 SELECT u.name, COALESCE(SUM(o.amount), 0) AS total_amount
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name;

複数テーブルの結合

3つ以上のテーブルを結合することができる。

 # 3つのテーブルを結合
 SELECT u.name, o.order_date, p.product_name
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id;
 
 # 4つ以上のテーブルを結合
 SELECT e.name, d.department_name, l.location_name, c.country_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
    INNER JOIN locations l ON d.location_id = l.id
    INNER JOIN countries c ON l.country_id = c.id;
 
 # INNER JOINとLEFT JOINの組み合わせ
 SELECT u.name, o.order_date, p.product_name
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    LEFT JOIN order_items oi ON o.id = oi.order_id
    LEFT JOIN products p ON oi.product_id = p.id;
 # すべてのユーザが表示される (注文がない場合はNULL)

結合順序は、オプティマイザーが自動的に最適化する。

 # EXPLAINで結合順序を確認
 EXPLAIN FORMAT=TREE
 SELECT u.name, o.order_date, p.product_name
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id;
 
 # JOIN_ORDERヒントで結合順序を指定
 SELECT /*+ JOIN_ORDER(u, o, oi, p) */ u.name, o.order_date, p.product_name
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id;

複数テーブル結合のパフォーマンス最適化の指針を以下に示す。

• 結合順序を考慮

  小さいテーブルを先に結合する。

• インデックスを適切に配置

  すべての結合列にインデックスを作成する。

• 不要なテーブルを結合しない

  必要な列のみを取得する。

• 派生テーブルで事前集計

  結合前にデータを削減する。

 # 派生テーブルで事前集計
 SELECT u.name, o.order_count, o.total_amount
    FROM users u
    INNER JOIN (
       SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
          FROM orders
          GROUP BY user_id
    ) AS o ON u.id = o.user_id;
 # ordersテーブルを集計してから結合することで、結合行数を削減

JOINと副問い合わせの比較

JOINと副問い合わせ (サブクエリ) は、多くの場合で同じ結果を得ることができる。

詳細な副問い合わせの使用方法については、MySQL - 副問い合わせのページを参照すること。

 # サブクエリ (IN)
 SELECT name FROM employees
    WHERE department_id IN (SELECT id FROM departments WHERE location = 'Tokyo');
 
 # JOINに書き換え
 SELECT DISTINCT e.name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
    WHERE d.location = 'Tokyo';
 
 # サブクエリ (NOT IN)
 SELECT name FROM users
    WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
 
 # LEFT JOINに書き換え
 SELECT u.name
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE o.user_id IS NULL;
 
 # サブクエリ (EXISTS)
 SELECT name FROM employees e
    WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.id);
 
 # INNER JOINに書き換え
 SELECT DISTINCT e.name
    FROM employees e
    INNER JOIN orders o ON o.employee_id = e.id;

使い分けの指針を以下に示す。

• 可読性を優先する場合

  サブクエリの方がわかりやすい場合がある。

• パフォーマンスが重要な場合

  JOINの方が高速なことが多い。

• 結合テーブルの列が必要な場合

  JOINを使用する。

• 存在チェックのみが必要な場合

  EXISTSまたはINが適している。

• NULLを含む可能性がある場合

  NOT INを避け、LEFT JOINまたはNOT EXISTSを使用する。

相関副問い合わせの詳細については、MySQL - 相関副問い合わせのページを参照すること。