カテゴリ：: クエリ構文

JOIN（結合）の操作¶

JOIN（結合）は、2つのテーブルの行を結合して、クエリで使用できる新しい結合行を作成します。

このトピックの内容:

概要
結合のタイプ
結合の実装

概要¶

結合は、テーブル内のデータが関連している場合に役立ちます。たとえば、1つのテーブルにプロジェクトに関する情報を保持し、1つのテーブルにそれらのプロジェクトで働いている従業員に関する情報を保持できます。

SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+

Copy

SELECT * FROM employees ORDER BY employee_ID;
+-------------+-----------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|-------------+-----------------+------------|
|    10000001 | Terry Smith     | 1000       |
|    10000002 | Maria Inverness | 1000       |
|    10000003 | Pat Wang        | 1001       |
|    10000004 | NewEmployee     | NULL       |
+-------------+-----------------+------------+

Copy

通常、2つの結合されたテーブルには1つ以上の列が共通して含まれているため、一方のテーブルの行を他方のテーブルの対応する行に関連付けることができます。例えば、プロジェクトテーブルの各行には一意のプロジェクト ID 番号があり、従業員テーブルの各行には、従業員が現在割り当てられているプロジェクトの ID 番号が含まれる場合があります。

結合操作は、通常プロジェクト ID などの共通の列を参照することにより、あるテーブルの行を他のテーブルの対応する行に関連付ける方法を（明示的または暗黙的に）指定します。例えば、次のように、上記のプロジェクトテーブルと従業員テーブルを結合します。

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p 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

1回の結合操作で結合できるのは2つのテーブルのみですが、結合はチェーン化できます。結合の結果はテーブルのようなオブジェクトであり、そのテーブルのようなオブジェクトは別のテーブルのようなオブジェクトに結合できます。概念的には、考え方は次のようになります（これは実際の構文ではありません）。

table1 join (table2 join table 3)

Copy

この擬似コードでは、table2（テーブル2）とtable3（テーブル3）が最初に結合されます。次に、その結合の結果であるテーブルがtable1（テーブル1）と結合されます。

結合は、テーブルだけでなく、他のテーブルのようなオブジェクトにも適用できます。以下のものを結合できます:

テーブル。
ビュー（マテリアライズドまたは非マテリアライズド）。
テーブルリテラル。
テーブル（1つ以上の列と0つ以上の行を含む）と同等に評価される式。例:
- テーブル関数によって返される結果セット。
- テーブルを返すサブクエリによって返される結果セット。

このトピックがテーブルの結合に言及している場合、それは通常、テーブルのようなオブジェクトを結合することを意味します。

注釈

Snowflakeは、不要な結合を削除してパフォーマンスを向上させることができます。詳細については、 Snowflakeが冗長な結合を削除する方法を理解するをご参照ください。

結合のタイプ¶

Snowflakeは、次の結合のタイプをサポートしています。

内部結合。
外部結合。
クロス結合。
自然結合。

内部結合¶

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

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

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            |
+------------------+-----------------+

Copy

従業員テーブルに一致する行がない場合でも、「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つのクエリは、異なる句（WHERE と FROM ... ON ...）で同じ条件（e.project_id = p.project_id）を使用すると同じ出力を生成しますが、同じ条件を使用するけれども、同じ出力を生成しないクエリのペアを作成する可能性があります。

最も一般的な例には、外部結合が含まれます。 table1 LEFT OUTER JOIN table2 を実行すると、一致しないテーブル1の行の場合、テーブル2から取得される列には NULL が含まれます。 WHERE table2.ID = table1.ID のようなフィルターは、テーブル2のIDまたはテーブル1のIDのいずれかに NULL が含まれている行をフィルターで除外しますが、 FROM ... ON ... 句の明示的な外部結合は NULL の行をフィルターで除外しません。つまり、フィルターを使用した外部結合は、実際には外部結合のように機能しない場合があります。

自然結合。¶

自然結合は、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 句を使用して自然結合の結果をフィルターできます。

結合の実装¶

構文的には、テーブルを結合する方法は2つあります。

FROM 句の ON サブ句で JOIN 演算子を使用します。
WHERE を FROM 句とともに使用します。

Snowflakeは、 FROM 句で ON サブ句を使用することをお勧めします。この構文はより柔軟です。また、 ON 句で述語を指定すると、 WHERE 句を使用して外部結合の結合条件を指定するときに、誤って NULLs で行をフィルターする問題を回避できます。