JOIN（結合）の操作¶

内部結合¶

内部結合は、一方のテーブルの各行と、もう一方のテーブルの一致する行をペアにします。

以下の例では、内部結合を使用しています。

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_id = p.project_id
    ORDER BY p.project_ID, e.employee_ID;
+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     | 1000       |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness | 1000       |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        | 1001       |
+------------+------------------+-------------+-----------------+------------+

Copy

この例では、出力テーブルに「Project_ID」という名前の2つの列が含まれています。1つのProject_ID 列はプロジェクトテーブルからのもので、もう1つは従業員テーブルからのものです。クエリで e.project_id = p.project_id が指定されているため、出力テーブルの各行について、2つのProject_ID 列の値が一致します。

出力には、有効なペア（つまり、結合条件に一致する行）のみが含まれます。この例では、「NewProject」という名前のプロジェクト（まだ従業員が割り当てられていない）の行、または「NewEmployee」という名前の従業員（まだどのプロジェクトにも割り当てられていない）の行はありません。

外部結合¶

外部結合は、指定されたテーブルのすべての行が他のテーブルに一致しない場合でも、それらの行をリストします。たとえば、プロジェクトと従業員の間の左外部結合では、まだ従業員が割り当てられていないプロジェクトを含むすべてのプロジェクトがリストされます。

SELECT p.project_name, e.employee_name
    FROM projects AS p LEFT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+

従業員テーブルに一致する行がない場合でも、「NewProject」という名前のプロジェクトがこの出力に含まれます。「NewProject」という名前のプロジェクトに一致する従業員名がないため、従業員名は NULL に設定されます。

右外部結合では、（プロジェクトに関係なく）すべての従業員がリストされます。

SELECT p.project_name, e.employee_name
    FROM projects AS p RIGHT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+

Copy

完全外部結合には、すべてのプロジェクトとすべての従業員がリストされます。

SELECT p.project_name, e.employee_name
    FROM projects AS p FULL OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+

Copy

クロス結合¶

クロス結合は、最初のテーブルの各行を2番目のテーブルの各行と組み合わせて、可能なすべての行の組み合わせを作成します（「デカルト積」と呼ばれます）。結果の行のほとんどには、実際には関連していない行の部分が含まれているため、クロス結合がそれ自体で役立つことはめったにありません。実際、クロス結合は通常、誤って結合条件を省略した結果です。

クロス結合の結果は非常に大きくなる可能性があります（そして高価になります）。最初のテーブルにN行があり、2番目のテーブルにM行がある場合、結果はN x M行になります。例えば、最初のテーブルに100行あり、2番目のテーブルに1000行ある場合、結果セットには100,000行が含まれます。

次のクエリは、クロス結合を示しています。

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Pat Wang        |
| COVID-19 Vaccine | NewEmployee     |
| Malaria Vaccine  | Terry Smith     |
| Malaria Vaccine  | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
| Malaria Vaccine  | NewEmployee     |
| NewProject       | Terry Smith     |
| NewProject       | Maria Inverness |
| NewProject       | Pat Wang        |
| NewProject       | NewEmployee     |
+------------------+-----------------+

Copy

このクエリには ON 句とフィルターが含まれていないことに注意してください。

クロス結合の出力は、 WHERE 句にフィルターを適用することでより便利になります。

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    WHERE e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Copy

このクロス結合とフィルターの結果は、次の内部結合の結果と同じです。

SELECT p.project_name, e.employee_name
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Copy

自然結合。¶

自然結合は、2つのテーブルに同じ名前の列が含まれ、それらの列のデータが対応する場合に使用します。上記の従業員テーブルとプロジェクトテーブルでは、両方のテーブルに「project_ID」という名前の列があります。自然結合は、暗黙的に ON 句: ON projects.project_ID = employees.project_ID を構成します。

2つのテーブルに共通の複数の列がある場合は、共通の列すべてが ON 句で使用されます。たとえば、それぞれに「city」と「province」という名前の列がある2つのテーブルがある場合、自然結合は次の ON 句を構築します。

ON table2.city = table1.city AND table2.province = table1.province。

自然結合の出力には、各共有列のコピーが1つだけ含まれます。たとえば、次のクエリは、冗長なproject_ID 列の1つのコピーを除くすべてを省略していることを除いて、2つのテーブルにあるすべての列を含む自然結合を生成します。

SELECT *
    FROM projects NATURAL JOIN employees
    ORDER BY employee_ID;
+------------+------------------+-------------+-----------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   |
|------------+------------------+-------------+-----------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |
+------------+------------------+-------------+-----------------+

Copy

自然結合は、外部結合と組み合わせることができます。

結合条件がすでに暗示されているため、自然結合を ON 句と組み合わせることはできません。ただし、 WHERE 句を使用して自然結合の結果をフィルターできます。