JOIN¶

構文¶

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

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

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

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

パラメーター¶

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 句を使用して結果をフィルタリングできます。

See moreShow less

Expand

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

• 結合順序ディレクティブを持つワークロードをSnowflakeに移行しています。

• 特定の順序で結合テーブルをスキャンしてパフォーマンスを向上させたいとします。

デフォルト: INNER JOIN

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

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

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

• LATERAL

• ASOF JOIN

ON condition

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

CopyExpand

ON object_ref2.id_number = object_ref1.id_number

Show lessSee more

Scroll to top

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

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

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

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

USING( column_list )

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

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

CopyExpand

... o1 JOIN o2
    USING (key_column)

Show lessSee more

Scroll to top

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

CopyExpand

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

Show lessSee more

Scroll to top

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

• USING 句の列を指定された順序で並べます。

• USING 句で指定されていない左側のテーブル列。

• USING 句で指定されていない右側のテーブル列。

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

使用上の注意¶

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

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

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

    CopyExpand

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

    Show lessSee more

    Scroll to top
    CopyExpand

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

    Show lessSee more

    Scroll to top
    CopyExpand

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

    Show lessSee more

    Scroll to top
    CopyExpand

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

    Show lessSee more

    Scroll to top

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

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

    CopyExpand

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

    Show lessSee more

    Scroll to top
    CopyExpand

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

    Show lessSee more

    Scroll to top
    CopyExpand

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

    Show lessSee more

    Scroll to top
    CopyExpand

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

    Show lessSee more

    Scroll to top

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

    Expand

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

    Show lessSee more

    Scroll to top

  • これらの制限は、JOIN キーワードではなくコンマを使用する場合は適用されません。

    CopyExpand

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

    Show lessSee more

    Scroll to top

例¶

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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');

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

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

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

CREATE TABLE t3 (col1 INTEGER);

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

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

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

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

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

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