- カテゴリ:
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> ]
[ ... ]
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各オブジェクト参照は、テーブルまたはテーブルのようなデータソースです。
JOINJOINキーワードを使用して、テーブルを結合する必要があることを指定します。JOINを他の結合関連キーワード(例:INNERまたはOUTER)と組み合わせて、結合のタイプを指定します。結合のセマンティクスは次のとおりです(簡潔にするため、このトピックでは
o1およびo2にそれぞれobject_ref1およびobject_ref2を使用します)。結合タイプ
セマンティクス
o1 INNER JOIN o2o1の各行には、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 o2o1およびo2の行のあらゆる可能な組み合わせ(つまり、デカルト積)のために、結合テーブルには、o1のすべての列とそれに続くo2のすべての列で構成される行が含まれます。CROSS JOINをON condition句と組み合わせることはできません。ただし、WHERE句を使用して結果をフィルタリングできます。o1 NATURAL JOIN o2NATURAL 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 JOINJOINまたは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
条件については、 WHERE 句のドキュメントで詳しく説明します。
ONのCROSS JOIN句は禁止されています。結合列が暗示されるため、
NATURAL JOINにはON句は不要であり、禁止されています。他の結合の場合、
ON句はオプションです。ただし、ON句を省略すると、デカルト積になります(object_ref1のすべての行はobject_ref2のすべての行とペア)。デカルト積は非常に大量の出力を生成する可能性があり、そのほとんどは実際には関係のない行のペアで構成されています。そのため、多量のリソースを消費し、多くの場合でユーザーエラーになります。USING( column_list )結合されている2つのテーブル間で共通している列のリスト。これらの列は結合列として使用されます。列は、結合される各テーブルで同じ名前と意味を持つ必要があります。
例えば、 SQL ステートメントに次が含まれているとします。
... o1 JOIN o2 USING (key_column)
単純な場合、これは次と同等になります。
... o1 JOIN o2 ON o2.key_column = o1.key_column
標準の 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 ... ;
SELECT ... FROM my_table INNER JOIN TABLE(FLATTEN(input=>[col_a])) ON ... ;
SELECT ... FROM my_table JOIN TABLE(my_js_udtf(col_a)) ON ... ;
SELECT ... FROM my_table INNER JOIN TABLE(my_js_udtf(col_a)) ON ... ;
テーブル関数への外側ラテラル結合では、SQL、UDTF を除いて、
ON、USING、NATURAL JOIN句は指定できません。たとえば、次の構文は許可されていません。
SELECT ... FROM my_table LEFT JOIN TABLE(FLATTEN(input=>[a])) ON ... ;
SELECT ... FROM my_table FULL JOIN TABLE(FLATTEN(input=>[a])) ON ... ;
SELECT ... FROM my_table LEFT JOIN TABLE(my_js_udtf(a)) ON ... ;
SELECT ... FROM my_table FULL JOIN TABLE(my_js_udtf(a)) ON ... ;
この構文を使用すると、次のようなエラーが発生します。
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 ... ;
例¶
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);
次の例では、結合を使用してクエリを実行します。
内部結合を使用してクエリを実行する¶
次の例では、内部結合を使用してクエリを実行します。
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 |
+------+------+
同じクエリを内部指向結合で実行し、結合順序を強制して左側のテーブルが最初にスキャンされるようにします。
注釈
ダイレクト結合は、 :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;
+------+------+
| 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;
出力には、テーブル 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;
出力には、テーブル 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;
各テーブルには他のテーブルに一致する行がない行があるため、出力には 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;
出力は、クエリがデカルト積を生成することを示しています。
+------+------+
| 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;
+------+------+
| 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');
自然結合を使用してクエリを実行します。
SELECT *
FROM d1 NATURAL INNER JOIN d2
ORDER BY id;
出力では、自然結合によって対応する内部結合と同じ出力が生成されますが、結合列の2番目のコピーは出力に含まれません。
+----+------+-------+
| 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 |
+----+------+-------+
FROM 句の結合を組み合わせるクエリを実行します。¶
FROM 句で組み合わせることができます。3番目のテーブルを作成します。
CREATE TABLE t3 (col1 INTEGER);
INSERT INTO t3 (col1) VALUES
(2),
(6);
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;
+------+------+------+
| 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 |
+------+------+------+
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);
クエリ例は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);
出力には2つの列が含まれ、2番目の列には2番目のテーブルの値または NULL が含まれます。
+------+------+
| UI_L | UI_R |
|------+------|
| a | NULL |
+------+------+