カテゴリ：: クエリ構文

JOIN¶

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

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

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

構文¶

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

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

Copy

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

Copy

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

Copy

object_ref1 および object_ref2

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

JOIN

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

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

結合タイプ	セマンティクス
`o1 INNER JOIN o2`	`o1` の各行には、 `ON condition` のサブ句に従って一致する `o2` の各行に対して行が生成されます。（コンマを使用して内部結合を指定することもできます。例については、以下の例をご参照ください。） `ON` 句なしで `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回だけ含まれます。（自然結合は、同じ名前で異なるテーブルの列に対応するデータが含まれていることを前提としています。）いくつかの例については、下記の例をご参照ください。 `NATURAL JOIN` は `OUTER JOIN` と組み合わせることができます。 `JOIN` 条件がすでに暗黙的に指定されているため、 `NATURAL JOIN` を `ON condition` 句と組み合わせることはできません。ただし、 `WHERE` 句を使用して結果をフィルタリングできます。

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

デフォルト： INNER JOIN

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

ON condition

一致すると見なされる JOIN の両側の行を定義するブール式。例えば、

ON object_ref2.id_number = object_ref1.id_number

Copy

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

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

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 句で指定されていない右側のテーブル列。

標準および非標準の使用例については、以下の例をご参照ください。

使用上の注意¶

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);
CREATE TABLE t2 (col1 INTEGER);

Copy

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

Copy

内部結合：

SELECT t1.col1, t2.col1
    FROM t1 INNER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Copy

これは、左外部結合を示しています。テーブルt2に一致する行がないテーブルt1の行の NULL 値に注意してください。

SELECT t1.col1, t2.col1
    FROM t1 LEFT OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+

Copy

これは、右外部結合を示しています。テーブルt2に一致する行がないテーブルt1の行の NULL 値に注意してください。

SELECT t1.col1, t2.col1
    FROM t1 RIGHT OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+

Copy

これは、完全な外部結合を示しています。各テーブルには他のテーブルに一致する行がない行があるため、出力には NULL 値を持つ2つの行が含まれます。

SELECT t1.col1, t2.col1
    FROM t1 FULL OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+

Copy

以下は直交結合の例で、デカルト積を生成します。クロス結合には ON 句がないことに注意してください。

SELECT t1.col1, t2.col1
    FROM t1 CROSS JOIN t2
    ORDER BY 1, 2;
+------+------+
| 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 |
+------+------+

Copy

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

SELECT t1.col1, t2.col1
    FROM t1 CROSS JOIN t2
    WHERE t2.col1 = t1.col1
    ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Copy

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

CREATE OR REPLACE TABLE d1 (
  id number,
  name string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D1 successfully created. |
+--------------------------------+
INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
CREATE OR REPLACE TABLE d2 (
  id number,
  value string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D2 successfully created. |
+--------------------------------+
INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT *
    FROM d1 NATURAL INNER JOIN d2
    ORDER BY id;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

Copy

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

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY ID;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+

Copy

結合は FROM 句で組み合わせることができます。次のコードは、3番目のテーブルを作成し、 FROM 句で2つの JOINs をチェーンします。

CREATE TABLE t3 (col1 INTEGER);
INSERT INTO t3 (col1) VALUES 
   (2),
   (6);

Copy

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;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+

Copy

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

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;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+

Copy

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

この最初の例は、標準的な使用法を示しています。具体的には、射影リストには「*」のみが含まれています。クエリ例は2つのテーブルを結合し、各テーブルには1つの列があり、クエリはすべての列をリクエストしますが、出力には2つではなく1つの列が含まれます。

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

Copy

次の例は、非標準の使用法を示しています。投影リストには「*」以外が含まれています。出力には2つの列が含まれ、2番目の列には2番目のテーブルの値または NULL が含まれます。

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)
;
+------+------+
| UI_L | UI_R |
|------+------|
| a    | NULL |
+------+------+

Copy