カテゴリ：: クエリ構文

JOIN¶

JOIN 操作により、2つのテーブル（またはビューやテーブル関数などの他のソース）の行を結合して、クエリで使用できる新しい結合された行が作成されます。結合の概念的な説明については、 JOIN（結合）の操作を参照してください。

このトピックでは、FROM 句で JOIN 副次句を使用する方法について説明します。JOIN 副次句は、あるテーブルの行を他のテーブルの対応する行に関連付ける方法を（明示的または暗黙的に）指定します。ASOF JOIN 副次句も使用できます。これは、タイムスタンプ値が互いに密接に続くか、先行するか、または完全に一致するときに、タイムスタンプ列の時系列データを結合するために使用されます。

テーブルを結合するための推奨方法は、 FROM 句の ON 節とともに JOIN を使用することですが、テーブルを結合する代替方法は、 WHERE 句を使用することです。詳細については、 WHERE 句のドキュメントをご参照ください。

構文¶

次のいずれかを使用します。

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                     [ DIRECTED ]
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]

Copy

SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                     [ DIRECTED ]
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]

Copy

SELECT ...
FROM <object_ref1> [
                     {
                       NATURAL [
                                 {
                                   INNER
                                   | { LEFT | RIGHT | FULL } [ OUTER ]
                                 }
                                 [ DIRECTED ]
                               ]
                       | CROSS  [ DIRECTED ]
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

Copy

パラメーター¶

object_ref1 および object_ref2

各オブジェクト参照は、テーブルまたはテーブルのようなデータソースです。

JOIN

JOIN キーワードを使用して、テーブルを結合する必要があることを指定します。JOIN を他の結合関連キーワード（例： INNER または OUTER ）と組み合わせて、結合のタイプを指定します。

結合のセマンティクスは次のとおりです（簡潔にするため、このトピックでは o1 および o2 にそれぞれ object_ref1 および object_ref2 を使用します）。

結合タイプ	セマンティクス
`o1 INNER JOIN o2`	`o1` の各行には、 `o2` のサブ句に従って一致する `ON condition` の各行に対して行が生成されます。（コンマを使用して内部結合を指定することもできます。例については、以下の :ref:` セクション <label-join_examples>` をご参照ください。）:code:`ON` 句なしで:code:`INNER JOIN` を使用する場合、または `WHERE` 句なしでコンマを使用する場合、結果は、デカルト積（`o1` のすべての行が `o2` のすべての行とペア）である `CROSS JOIN` を使用する場合と同じです。
`o1 LEFT OUTER JOIN o2`	内部結合の結果は、 `o2` で一致しない `o1` の行ごとに1行追加されます。`o2` を参照する結果列にnullが含まれています。
`o1 RIGHT OUTER JOIN o2`	内部結合の結果は、 `o1` で一致しない `o2` の行ごとに1行追加されます。`o1` を参照する結果列にnullが含まれています。
`o1 FULL OUTER JOIN o2`	結合されたすべての行に加えて、一致しない左側の行ごとに1行（右側にnullが拡張）、および一致しない右側の行ごとに1行（左側にnullが拡張）を返します。
`o1 CROSS JOIN o2`	`o1` および `o2` の行のあらゆる可能な組み合わせ（つまり、デカルト積）のために、結合テーブルには、 `o1` のすべての列とそれに続く `o2` のすべての列で構成される行が含まれます。`CROSS JOIN` を `ON condition` 句と組み合わせることはできません。ただし、 `WHERE` 句を使用して結果をフィルタリングできます。
`o1 NATURAL JOIN o2`	`NATURAL JOIN` は、2つのテーブルの共通列の明示的な `JOIN` と同じですが、共通列は出力に1回だけ含まれます。（自然結合は、同じ名前で異なるテーブルの列に対応するデータが含まれていることを前提としています。）例については、:ref:` 例セクション <label-join_examples>` をご参照ください。`NATURAL JOIN` は `OUTER JOIN` と組み合わせることができます。`JOIN` 条件がすでに暗黙的に指定されているため、 `NATURAL JOIN` を `ON condition` 句と組み合わせることはできません。ただし、 `WHERE` 句を使用して結果をフィルタリングできます。

その DIRECTED キーワードは、テーブルの結合順序を強制する ダイレクト結合 を指定します。最初のテーブルまたは左側のテーブルは、2番目のテーブルまたは右側のテーブルの前にスキャンされます。例: o1 INNER DIRECTED JOIN o2 は o1 テーブルを o2 テーブルの前にスキャンします。ダイレクト結合は、次のような場合に便利です。

結合順序ディレクティブを持つワークロードをSnowflakeに移行しています。
特定の順序で結合テーブルをスキャンしてパフォーマンスを向上させたいとします。

注釈

ダイレクト結合は、 :doc:` プレビュー機能 </release-notes/preview-features>` であり、すべてのアカウントで利用できます。

デフォルト: INNER JOIN

JOIN または INNER を指定せずに単語 OUTER を使用すると、 JOIN は内部結合になります。

DIRECTED キーワードが追加された場合、たとえば INNER、LEFT、RIGHT、または FULL のような結合タイプは必須です。

こちらもご参照ください。

ON condition

一致すると見なされる JOIN の両側の行を定義する :doc:` ブール式 </sql-reference/data-types-logical>`。例えば、

ON object_ref2.id_number = object_ref1.id_number

Copy

条件については、 WHERE 句のドキュメントで詳しく説明します。

ON の CROSS JOIN 句は禁止されています。

結合列が暗示されるため、NATURAL JOIN には ON 句は不要であり、禁止されています。

他の結合の場合、 ON 句はオプションです。ただし、ON 句を省略すると、デカルト積になります（object_ref1 のすべての行は object_ref2 のすべての行とペア）。デカルト積は非常に大量の出力を生成する可能性があり、そのほとんどは実際には関係のない行のペアで構成されています。そのため、多量のリソースを消費し、多くの場合でユーザーエラーになります。

USING( column_list )

結合されている2つのテーブル間で共通している列のリスト。これらの列は結合列として使用されます。列は、結合される各テーブルで同じ名前と意味を持つ必要があります。

例えば、 SQL ステートメントに次が含まれているとします。

... o1 JOIN o2
    USING (key_column)

Copy

単純な場合、これは次と同等になります。

... o1 JOIN o2
    ON o2.key_column = o1.key_column

Copy

標準の JOIN 構文では、投影リスト（SELECT キーワードの後の列およびその他の式のリスト）は「*」です。これにより、クエリは key_column を1回だけ返します。列は以下の順序で返されます。

USING 句の列を指定された順序で並べます。
USING 句で指定されていない左側のテーブル列。
USING 句で指定されていない右側のテーブル列。

標準および非標準の使用例については、:ref:` 例セクション <label-join_examples>` をご参照ください。

使用上の注意¶

SQL UDTFs 以外のテーブル関数には、以下の制限があります。

ラテラルテーブル関数では、SQL、UDTF を除いて、ON、USING、NATURAL JOIN 句は指定できません。

たとえば、次の構文は許可されていません。

SELECT ... FROM my_table
  JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  INNER JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  JOIN TABLE(my_js_udtf(col_a))
  ON ... ;

Copy

SELECT ... FROM my_table
  INNER JOIN TABLE(my_js_udtf(col_a))
  ON ... ;

Copy

テーブル関数への外側ラテラル結合では、SQL、UDTF を除いて、ON、USING、NATURAL JOIN 句は指定できません。

たとえば、次の構文は許可されていません。

SELECT ... FROM my_table
  LEFT JOIN TABLE(FLATTEN(input=>[a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  FULL JOIN TABLE(FLATTEN(input=>[a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  LEFT JOIN TABLE(my_js_udtf(a))
  ON ... ;

Copy

SELECT ... FROM my_table
  FULL JOIN TABLE(my_js_udtf(a))
  ON ... ;

Copy

この構文を使用すると、次のようなエラーが発生します。

000002 (0A000): Unsupported feature
  'lateral table function called with OUTER JOIN syntax
   or a join predicate (ON clause)'

これらの制限は、JOIN キーワードではなくコンマを使用する場合は適用されません。
```
SELECT ... FROM my_table,
  TABLE(FLATTEN(input=>[col_a]))
  ON ... ;
```
Copy

例¶

JOIN の例の多くは、t1 と t2 の2つのテーブルを使用しています。これらのテーブルを作成してデータを挿入します。

CREATE TABLE t1 (col1 INTEGER);

INSERT INTO t1 (col1) VALUES
  (2),
  (3),
  (4);

CREATE TABLE t2 (col1 INTEGER);

INSERT INTO t2 (col1) VALUES
  (1),
  (2),
  (2),
  (3);

Copy

次の例では、結合を使用してクエリを実行します。

内部結合を使用してクエリを実行する
左外部結合を使用してクエリを実行する
右外部結合を使用してクエリを実行する
完全外部結合を使用してクエリを実行する
クロス結合でクエリを実行する
自然結合を使用してクエリを実行する
FROM 句の結合を組み合わせるクエリを実行します。
USING 句を使用する結合でクエリを実行する

内部結合を使用してクエリを実行する¶

次の例では、内部結合を使用してクエリを実行します。

SELECT t1.col1, t2.col1
  FROM t1 INNER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

同じクエリを内部指向結合で実行し、結合順序を強制して左側のテーブルが最初にスキャンされるようにします。

注釈

ダイレクト結合は、 :doc:` プレビュー機能 </release-notes/preview-features>` であり、すべてのアカウントで利用できます。

SELECT t1.col1, t2.col1
  FROM t1 INNER DIRECTED JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

左外部結合を使用してクエリを実行する¶

次の例では、左外部結合でクエリを実行します。

SELECT t1.col1, t2.col1
  FROM t1 LEFT OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

出力には、テーブル t2 に一致する行がないテーブル t1 の行の NULL 値があります。

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+

右外部結合を使用してクエリを実行する¶

次の例では、右外部結合を使用してクエリを実行します。

SELECT t1.col1, t2.col1
  FROM t1 RIGHT OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

出力には、テーブル t2 に一致する行がないテーブル t1 の行の NULL 値があります。

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+

完全外部結合を使用してクエリを実行する¶

次の例では、完全外部結合を使用してクエリを実行します。

SELECT t1.col1, t2.col1
  FROM t1 FULL OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

各テーブルには他のテーブルに一致する行がない行があるため、出力には NULL 値を持つ2つの行が含まれます。

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+

クロス結合でクエリを実行する¶

次の例では、クロス結合でクエリを実行します。

注釈

クロス結合には ON 句がありません。

SELECT t1.col1, t2.col1
  FROM t1 CROSS JOIN t2
  ORDER BY 1, 2;

Copy

出力は、クエリがデカルト積を生成することを示しています。

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    1 |
|    2 |    2 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    2 |
|    3 |    3 |
|    4 |    1 |
|    4 |    2 |
|    4 |    2 |
|    4 |    3 |
+------+------+

次の例に示すように、クロス結合は WHERE 句でフィルタリングできます。

SELECT t1.col1, t2.col1
  FROM t1 CROSS JOIN t2
  WHERE t2.col1 = t1.col1
  ORDER BY 1, 2;

Copy

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

自然結合を使用してクエリを実行する¶

次の例は、自然結合を使用したクエリを示しています。まず、テーブルを2つ作成してデータを挿入します。

CREATE OR REPLACE TABLE d1 (
  id NUMBER,
  name VARCHAR);

INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');

CREATE OR REPLACE TABLE d2 (
  id NUMBER,
  value VARCHAR);

INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');

Copy

自然結合を使用してクエリを実行します。

SELECT *
  FROM d1 NATURAL INNER JOIN d2
  ORDER BY id;

Copy

出力では、自然結合によって対応する内部結合と同じ出力が生成されますが、結合列の2番目のコピーは出力に含まれません。

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

次の例は、自然結合と外部結合を組み合わせることができることを示しています。

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY id;

Copy

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+

FROM 句の結合を組み合わせるクエリを実行します。¶

FROM 句で組み合わせることができます。3番目のテーブルを作成します。

CREATE TABLE t3 (col1 INTEGER);

INSERT INTO t3 (col1) VALUES
  (2),
  (6);

Copy

FROM 句の2つの結合をチェーンするクエリを実行します。

SELECT t1.*, t2.*, t3.*
  FROM t1
    LEFT OUTER JOIN t2 ON (t1.col1 = t2.col1)
    RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1)
  ORDER BY t1.col1;

Copy

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+

このようなクエリでは、結果は左から右への結合に基づいて決定されます。ただし、異なる結合順序で同じ結果が生成される場合は、オプティマイザーが結合を並べ替える可能性があります。右外部結合が左外部結合の前に行われることを意図している場合、クエリは次のように記述します。

SELECT t1.*, t2.*, t3.*
FROM t1
  LEFT OUTER JOIN
    (t2 RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1))
  ON (t1.col1 = t2.col1)
ORDER BY t1.col1;

Copy

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+

USING 句を使用する結合でクエリを実行する¶

以下の2つの例は、USING 句の標準（ISO 9075）および非標準の使用法を示しています。どちらもSnowflakeでサポートされています。

この最初の例は、標準的な使用法を示しています。具体的には、投影リストには「*」のみが含まれています。

WITH
  l AS (
       SELECT 'a' AS userid
       ),
  r AS (
       SELECT 'b' AS userid
       )
SELECT *
  FROM l LEFT JOIN r USING(userid);

Copy

クエリ例は2つのテーブルを結合し、各テーブルには1つの列があり、クエリはすべての列をリクエストしますが、出力には2つではなく1つの列が含まれます。

+--------+
| USERID |
|--------|
| a      |
+--------+

次の例は、非標準の使用法を示しています。投影リストには「*」以外が含まれています。

WITH
  l AS (
       SELECT 'a' AS userid
     ),
  r AS (
       SELECT 'b' AS userid
       )
SELECT l.userid as UI_L,
       r.userid as UI_R
  FROM l LEFT JOIN r USING(userid);

Copy

出力には2つの列が含まれ、2番目の列には2番目のテーブルの値または NULL が含まれます。

+------+------+
| UI_L | UI_R |
|------+------|
| a    | NULL |
+------+------+