カテゴリ:

クエリ構文

JOIN(結合)の操作

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

このトピックの内容:

概要

2つのテーブルを結合する場合、2つのテーブルのデータは何らかの形で関連しています。例えば、1つのテーブルにプロジェクトに関する情報を保持し、1つのテーブルにそれらのプロジェクトで働いている従業員に関する情報を保持できます。

+------------+------------------+
| Project_ID | Project_Name     |
+------------+------------------+
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+

+-------------+------------------+------------+
| Employee_ID | Employee_Name    | Project_ID |
+-------------+------------------+------------+
|    10000001 | Terry Smith      |       1000 |
|    10000002 | Maria Inverness  |       1000 |
|    10000003 | Pat Wang         |       1001 |
|    99999999 | NewEmployee      |       NULL |
+-------------+------------------+------------+

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

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

SELECT *
    FROM projects AS p JOIN employees AS e
        ON e.project_ID = p.project_ID;

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

table1 join (table2 join table 3)

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

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

  • テーブル。

  • ビュー (マテリアライズドまたはマテリアライズドでないもの)。

  • テーブルリテラル

  • テーブル(1つ以上の列と0つ以上の行を含む)と同等に評価される式。例:

    • テーブル関数 によって返される結果セット。

    • テーブルを返すサブクエリによって返される結果セット。

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

結合のタイプ

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

  • 内部結合。

  • 外部結合。

  • クロス結合。

  • 自然結合。

内部結合

一方のテーブルの各行は、もう一方のテーブルの一致する行とペアになっています。

上記のテーブルと以下に示すクエリを使用するとします。

SELECT *
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_id = p.project_id;

結果は次のようになります。

+-------------+------------------+------------+------------+------------------+
| Employee_ID | Employee_Name    | Project_ID | Project_ID | Project_name     |
+-------------+------------------+------------+------------+------------------+
|    10000001 | Terry Smith      |       1000 |       1000 | COVID-19 Vaccine |
|    10000002 | Maria Inverness  |       1001 |       1001 | Malaria Vaccine  |
|    10000003 | Pat Wang         |       1001 |       1001 | Malaria Vaccine  |
+-------------+------------------+------------+------------+------------------+

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

出力には、有効なペア、つまり結合条件に一致する行のみが含まれます。この例では、(まだ従業員が割り当てられていない)新しいプロジェクトまたは(まだどのプロジェクトにも割り当てられていない)新しい従業員の行はありません。

外部結合

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

SELECT p.project_name, e.employee_name
    FROM projects AS p LEFT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID;

結果は次のようになります。

+------------------+------------------+
| Project_name     | Employee_Name    |
+------------------+------------------+
| COVID-19 Vaccine | Terry Smith      |
| Malaria Vaccine  | Maria Inverness  |
| Malaria Vaccine  | Pat Wang         |
| New Project      | NULL             |
+------------------+------------------+

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

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

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

+------------------+------------------+
| Project_name     | Employee_Name    |
+------------------+------------------+
| COVID-19 Vaccine | Terry Smith      |
| Malaria Vaccine  | Maria Inverness  |
| Malaria Vaccine  | Pat Wang         |
| New Project      | NULL             |
| NULL             | NewEmployee      |
+------------------+------------------+

クロス結合

クロス結合は、最初のテーブルの各行を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;

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

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

SELECT p.project_name, e.employee_name
    FROM projects AS p INNER JOIN employees AS e
    WHERE e.project_ID = p.project_ID;

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

SELECT p.project_name, e.employee_name
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_ID = p.project_ID;

重要

この例の2つのクエリは、異なる句(WHEREFROM ... 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つのテーブルに同じ名前の列が含まれ、それらの列のデータが対応する場合に使用されます。例えば、2つのテーブルがあり、それぞれに「City(市)」という名前の列と「Province(県)」という名前の列が含まれているとします。自然結合は、暗黙的に ONON table2.city = table1.city AND table2.province = table1.province を構成します。

自然結合では、これらの各列のコピーが1つだけ出力に自動的に含まれます。

例えば、次のクエリは、2つのテーブルのすべての列を含む自然結合を生成しますが、テーブルの1つから冗長な市と県の情報を省略します。

SELECT *
    FROM geographic_data_by_city_and_province NATURAL JOIN demographic_data_by_city_and_province;

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

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

結合の実装

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

  • FROM 句の ON サブ句で JOIN 演算子を使用します。

  • WHEREFROM 句とともに使用します。

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