カテゴリ:

クエリ構文

JOIN

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

このトピックでは、 FROM 句で JOIN 構文を使用する方法について説明します。 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 JOINON condition 句と組み合わせることはできません。ただし、 WHERE 句を使用して結果をフィルタリングできます。

o1 NATURAL JOIN o2

NATURAL JOIN は、2つのテーブルの共通列の明示的な JOIN と同じですが、共通列は出力に1回だけ含まれます。(自然結合は、同じ名前で異なるテーブルの列に対応するデータが含まれていることを前提としています。)いくつかの例については、下記の例をご参照ください。 NATURAL JOINOUTER JOIN と組み合わせることができます。 JOIN 条件がすでに暗黙的に指定されているため、 NATURAL JOINON condition 句と組み合わせることはできません。ただし、 WHERE 句を使用して結果をフィルタリングできます。

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

ラテラル結合

デフォルト: INNER JOIN

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

ON condition

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

ON object_ref2.id_number = object_ref1.id_number
Copy

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

CROSS JOINON 句は禁止されています。

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回だけ返します。標準および非標準の使用例については、以下の例をご参照ください。

使用上の注意

  • 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 の例の多くは、 t1t2 の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