カテゴリ:

クエリ構文

JOIN(結合)の操作

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

このトピックの内容:

概要

Joins are useful when the data in the tables is related. For example, one table might hold information about projects, and one table might hold information about employees working on those projects.

SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+
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       |
+-------------+-----------------+------------+

通常、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       |
+------------+------------------+-------------+-----------------+------------+

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

table1 join (table2 join table 3)

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

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

  • テーブル。

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

  • テーブルリテラル

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

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

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

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

結合のタイプ

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

  • 内部結合。

  • 外部結合。

  • クロス結合。

  • 自然結合。

内部結合

An inner join pairs each row in one table with the matching row(s) in the other table.

The example below uses an inner 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       |
+------------+------------------+-------------+-----------------+------------+

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

The output includes only valid pairs (i.e. rows that match the join condition). In this example there is no row for the project named "NewProject" (which has no employees assigned yet) or the employee named "NewEmployee" (who hasn't been assigned to any projects yet).

外部結合

An outer join lists all rows in the specified table, even if those rows have no match in the other table. For example, a left outer join between projects and employees lists all projects, including projects that do not yet have any employee assigned.

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

The project named "NewProject" is included in this output even though there is no matching row in the employees table. Because there are no matching employee names for the project named "NewProject", the employee name is set to NULL.

A right outer join lists all employees (regardless of project).

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

A full outer join lists all projects and all employees.

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

クロス結合

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

このクエリには 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        |
+------------------+-----------------+

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

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

重要

この例の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 の行をフィルターで除外しません。つまり、フィルターを使用した外部結合は、実際には外部結合のように機能しない場合があります。

自然結合。

A natural join is used when two tables contain columns that have the same name and in which the data in those columns corresponds. In the employees and projects tables shown above, both tables have columns named "project_ID". A natural join implicitly constructs the ON clause: ON projects.project_ID = employees.project_ID.

If two tables have multiple columns in common, then all the common columns are used in the ON clause. For example, if you had two tables that each had columns named "city" and "province", then a natural join would construct the following ON clause:

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

The output of a natural join includes only one copy of each of the shared columns. For example, the following query produces a natural join containing all columns in the two tables, except that it omits all but one copy of the redundant project_ID column:

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

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

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

結合の実装

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

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

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

Snowflake recommends using the ON sub-clause in the FROM clause. The syntax is more flexible. And specifying the predicate in the ON clause avoids the problem of accidentally filtering rows with NULLs when using a WHERE clause to specify the join condition for an outer join.