MySQL 入門

MySQL 多表クエリ進階：JOIN と WHERE の組み合わせ

複雑なクエリでは、単に複数のテーブルを結合（JOIN）するだけでなく、特定の結果セットを抽出するためにデータをフィルタリングする必要があります。JOIN 操作と WHERE 句を組み合わせることで、関連する複数のテーブルから返されるデータを正確にコントロールし、高度に具体的かつ効率的なクエリを構築することが可能になります。この組み合わせは、相互に関連するデータセットから必要な情報を正確に抽出するための、実務におけるデータベース操作の根幹となります。

1. JOIN後のデータをWHERE句でフィルタリングする

モジュール3で学んだように、WHERE 句は指定した条件に基づいて行をフィルタリングするために使用されます。JOIN ステートメントと一緒に使用する場合、WHERE 句はテーブルが結合された後に適用されます。つまり、フィルタリング条件には、JOIN 操作に参加しているすべてのテーブルのカラムを参照させることができます。この「結合後のフィルタリング」は、結果の範囲を絞り込むための強力なメカニズムです。

1.1 INNER JOINとの基本的な組み合わせ

INNER JOIN（インナージョイン）は、両方のテーブルで一致する値を持つ行のみを取得します。これに WHERE 句を追加することで、追加の基準に基づき、一致した行をさらに制限することができます。

例 1：特定の顧客とその注文を検索する
顧客（customers）テーブルと注文（orders）テーブルを含むEコマースのデータベースを想定します。'Maria Anders' という名前の顧客が出した注文を特定したい場合：

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM
    customers AS c
INNER JOIN
    orders AS o ON c.customer_id = o.customer_id
WHERE
    c.customer_name = 'Maria Anders';
  • FROM ... INNER JOIN ... ON ...: この部分は、まず共通の customer_id に基づいて customers と orders テーブルを結合します。エイリアス AS c と AS o を使用することで、クエリの可読性を高めています。
  • WHERE c.customer_name = 'Maria Anders': ジョイン操作によって一時的な複合データセットが作成された後、この WHERE 句が結果をフィルタリングし、customers テーブルの customer_name が 'Maria Anders' である行のみを表示します。

例 2：注文数量が特定の範囲内にある製品を検索する

製品（products）テーブルと注文明細（order_items）テーブルがあるとします。注文明細の一部であり、かつ注文数量（quantity）が 5 から 10 の間である製品を確認したい場合：

SELECT
    p.product_name,
    oi.quantity,
    oi.price_per_unit
FROM
    products AS p
INNER JOIN
    order_items AS oi ON p.product_id = oi.product_id
WHERE
    oi.quantity BETWEEN 5 AND 10;

INNER JOIN が product_id に基づいてデータを組み合わせ、その後 WHERE 句が結合後の行をフィルタリングし、数量が指定範囲内の行のみを抽出します。

2. LEFT JOIN および RIGHT JOIN との組み合わせ

外部結合（LEFT JOIN または RIGHT JOIN）を使用する場合も、WHERE 句は結合後に適用されます。ただし、外部結合によって導入される NULL 値の挙動については注意深い考慮が必要です。

例 3：プロジェクトが割り当てられていない特定部署の従業員を検索する

従業員（employees）テーブルとプロジェクト（projects）テーブルを想定します。employees から projects への LEFT JOIN を使用してすべての従業員とそのプロジェクトを取得しますが、特に 'Marketing'（マーケティング部）に所属し、かつプロジェクトが一つも割り当てられていない従業員を特定したい場合：

SELECT
    e.employee_id,
    e.employee_name,
    e.department,
    p.project_name
FROM
    employees AS e
LEFT JOIN
    projects AS p ON e.employee_id = p.assigned_employee_id
WHERE
    e.department = 'Marketing' AND p.project_id IS NULL;
  • LEFT JOIN によりすべての従業員が確実にリストアップされ、プロジェクトが割り当てられていない場合はプロジェクト関連のカラムが NULL になります。
  • WHERE e.department = 'Marketing' AND p.project_id IS NULL: これにより、部署が 'Marketing' かつ project_id が存在しない（外部結合によって NULL になっている、つまりプロジェクト未割り当てを意味する）従業員のみをフィルタリングします。

重要な落とし穴： LEFT JOIN の後、WHERE 句で右テーブルのカラムに対して通常の比較演算（例：p.status = 'Active'）を使用すると、NULL を含む行が暗黙的に除外されてしまいます。これにより、せっかくの LEFT JOIN が実質的に INNER JOIN と同じ挙動になってしまいます。左テーブルの全行を保持しつつ右テーブルに条件を付けたい場合は、その条件を WHERE 句ではなく ON 句 に記述する必要があります。

3. 複数条件と複数テーブル結合による高度なフィルタリング

複数の JOIN 句と WHERE 句を組み合わせることで、複数のテーブルを跨いだ非常に精密なデータ取得が可能になります。WHERE 句には AND、OR、NOT 演算子で接続された複数の条件を含めることができます。

例 4：3つのテーブルを跨いで特定の注文詳細を検索する

Eコマースの例を拡張し、customers、orders、order_items の3つを結合します。'John Doe' が出した注文の中で、注文総額（total_amount）が 100 ドルを超えるすべての注文明細の製品名と数量を特定したい場合：

SELECT
    c.customer_name,
    o.order_id,
    o.total_amount,
    oi.product_id,
    oi.quantity
FROM
    customers AS c
INNER JOIN
    orders AS o ON c.customer_id = o.customer_id
INNER JOIN
    order_items AS oi ON o.order_id = oi.order_id
WHERE
    c.customer_name = 'John Doe' AND o.total_amount > 100;

このクエリでは2回の INNER JOIN 操作が実行されます。その後、WHERE 句が「顧客名」と「注文総額」の2つの条件に基づき、広範囲に結合されたデータセットをフィルタリングします。

4. 実行順序：JOIN vs. WHERE

SQL クエリにおける論理的な操作順序を理解することは非常に重要です。概念的には、JOIN 操作は WHERE 句が適用される前に発生します。

  1. FROM / JOINs: MySQL はまず対象となるテーブルを特定し、JOIN 条件に基づいてそれらを組み合わせます。これにより、一時的な複合結果セットが作成されます。
  2. WHERE: 結合されたテーブルが形成された後、WHERE 句が指定された条件に従ってこの複合結果セットの行をフィルタリングします。WHERE 条件を満たす行のみが次に渡されます。
  3. SELECT: 最終的に、SELECT 句がフィルタリング済みの行からどのカラムを最終出力として表示するかを決定します。