カテゴリ：: クエリ構文

JOIN（結合）の操作¶

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

概要¶

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

CREATE TABLE projects (
  project_id INT,
  project_name VARCHAR);

INSERT INTO projects VALUES
  (1000, 'COVID-19 Vaccine'),
  (1001, 'Malaria Vaccine'),
  (1002, 'NewProject');

CREATE TABLE employees (
  employee_id INT,
  employee_name VARCHAR,
  project_id INT);

INSERT INTO employees VALUES
  (10000001, 'Terry Smith', 1000),
  (10000002, 'Maria Inverness', 1000),
  (10000003, 'Pat Wang', 1001),
  (10000004, 'NewEmployee', NULL);

テーブルをクエリしてデータを表示します。

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 番号が含まれています。

結合操作は、通常プロジェクト project_id などの共通の列を参照することにより、あるテーブルの行を他のテーブルの対応する行に関連付ける方法を（明示的または暗黙的に）指定します。たとえば、次は projects および employees 以前に作成されたテーブルを結合します。：

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 table3)

この擬似コードでは、次のようになります。 table2 および table3 は最初に結合されます。次に、その結合の結果であるテーブルが table1 と結合されます。

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

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

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

注釈

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

結合のタイプ¶

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

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

注釈

Snowflakeは ASOFJOIN 時系列データの分析もサポートしています。詳細については、 ASOF 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 列は、 projects テーブルにあり、そのうちの1つは employees テーブルにあります。出力テーブルの各行について、クエリで 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            |
+------------------+-----------------+

employees テーブルに一致する行がない場合でも、「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     |
+------------------+-----------------+

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

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行が含まれます。

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

注釈

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

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

クロス結合の出力は、 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つのクエリは、異なる句（e.project_id = p.project_id と WHERE）で同じ条件（FROM ... ON ...）を使用すると同じ出力を生成しますが、同じ条件を使用するけれども、同じ出力を生成しないクエリのペアを作成する可能性があります。

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

自然結合。¶

自然結合は、同じ名前と互換性のあるデータ型の列で2つのテーブルを結合します。以前に作成された employees および projects テーブルには、両方とも project_ID という名前の列があります。自然結合は、暗黙的に ON 句: ON projects.project_ID = employees.project_ID を構成します。

2つのテーブルに共通の複数の列がある場合、自然結合は構築された共通列すべてが ON 句で使用されます。たとえば、2つのテーブルにそれぞれ city および province という名前の列がある場合、自然結合は次を構築します。 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        |
+------------+------------------+-------------+-----------------+

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

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

結合の実装¶

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

JOIN のサブ句 ON のサブ句 FROM 句を使用する。
WHERE を使用した句 FROM 句を使用する。

構文の柔軟性があるため、Snowflakeは ON のなかで FROM サブ句の使用を推奨します。また、 ON 句で述語を指定すると、 FROM 句を使用して外部結合の結合条件を指定するときに、誤って ON で行をフィルターする問題を回避できます。

さらに、 DIRECTED キーワードを使用して、テーブルの結合順序を強制できます。このキーワードを指定すると、2番目または右側のテーブルの前に最初または左側のテーブルがスキャンされます。例: o1 INNER DIRECTED JOIN o2 は o1 テーブルを o2 テーブルの前にスキャンします。DIRECTED キーワードが追加された場合、結合タイプ --- 例えば INNER または OUTER --- は必須です。詳細については、 JOIN をご参照ください。