メインコンテンツまでスキップ
メインコンテンツまでスキップ

JOIN句

JOINは、1つまたは複数のテーブルから共通の値を使用してカラムを結合することで新しいテーブルを生成します。これは、SQLをサポートするデータベースにおける一般的な操作であり、関係代数のJOINに相当します。1つのテーブルのJOINの特別なケースは、一般的に「自己結合」と呼ばれます。

構文

ON句の式およびUSING句のカラムは「結合キー」と呼ばれます。特に指定がない限り、JOINは「結合キー」が一致する行からのデカルト積を生成し、これによりソーステーブルよりもはるかに多くの行を持つ結果が生成される可能性があります。

関連コンテンツ​

サポートされているJOINの種類​

すべての標準SQL JOINタイプがサポートされています：

  • INNER JOIN、一致する行のみが返されます。
  • LEFT OUTER JOIN、左テーブルから一致する行に加えて、非一致の行も返されます。
  • RIGHT OUTER JOIN、右テーブルから一致する行に加えて、非一致の行も返されます。
  • FULL OUTER JOIN、両方のテーブルから一致する行に加えて、非一致の行も返されます。
  • CROSS JOIN、全テーブルのデカルト積を生成し、「結合キー」は指定されません。

指定されたタイプなしのJOINはINNERを意味します。キーワードOUTERは安全に省略できます。CROSS JOINの代替構文は、FROM句でカンマで区切った複数のテーブルを指定することです。

ClickHouseで使用可能な追加のJOINタイプ：

  • LEFT SEMI JOINおよびRIGHT SEMI JOIN、デカルト積を生成せずに「結合キー」のホワイトリスト。
  • LEFT ANTI JOINおよびRIGHT ANTI JOIN、デカルト積を生成せずに「結合キー」のブラックリスト。
  • LEFT ANY JOIN、RIGHT ANY JOINおよびINNER ANY JOIN、デカルト積を部分的（LEFTとRIGHTの反対側の場合）または完全に（INNERとFULLの場合）無効にします。
  • ASOF JOINおよびLEFT ASOF JOIN、厳密には一致しないシーケンスを結合します。ASOF JOINの使用は以下に示されています。
  • PASTE JOIN、二つのテーブルの水平方向の連結を行います。
注記

join_algorithmがpartial_mergeに設定されている場合、RIGHT JOINおよびFULL JOINはALLの厳密度でのみサポートされています（SEMI、ANTI、ANYおよびASOFはサポートされていません）。

設定​

デフォルトのJOINタイプは、join_default_strictness設定を使用してオーバーライドできます。

ANY JOIN操作におけるClickHouseサーバーの動作は、any_join_distinct_right_table_keys設定に依存します。

参考

cross_to_inner_join_rewrite設定を使用して、ClickHouseがCROSS JOINをINNER JOINとして書き換えられなかった場合の動作を定義します。デフォルト値は1で、これによりJOINが続行されますが、遅くなります。エラーをスローさせたい場合はcross_to_inner_join_rewriteを0に設定し、全てのカンマ/CROSS JOINの書き換えを強制するには2に設定します。値が2のときに書き換えが失敗すると、「WHEREセクションを簡素化してください」というエラーメッセージが表示されます。

ONセクション条件​

ONセクションには、ANDおよびOR演算子を使用して結合された複数の条件を含めることができます。結合キーを指定する条件は、左側と右側の両方のテーブルを参照しなければならず、等号演算子を使用する必要があります。他の条件は別の論理演算子を使用できますが、クエリの左側または右側のテーブルのいずれかを参照する必要があります。

複合条件全体が満たされている場合に行が結合されます。条件が満たされない場合でも、JOINタイプに応じて結果に行が含まれる場合があります。同じ条件がWHEREセクションに配置されていて、満たされない場合は、行は常に結果からフィルタリングされます。

ON句内のOR演算子は、ハッシュ結合アルゴリズムを使用して機能します。JOINの結合キーに対する各OR引数に対して別々のハッシュテーブルが作成されるため、メモリ消費量とクエリ実行時間は、ON句のOR表現の数の増加に伴って線形に増加します。

注記

異なるテーブルからカラムを参照する条件がある場合、現時点では等号演算子（=）のみがサポートされています。

例

table_1とtable_2を考えます：

1つの結合キー条件とtable_2に対する追加条件を持つクエリ：

結果には、名前がCでテキストカラムが空の行が含まれます。これは、OUTERタイプのJOINが使用されているため、結果に含まれます。

INNERタイプのJOINと複数の条件を持つクエリ：

結果：

INNERタイプのJOINおよびORを含む条件のクエリ：

結果：

INNERタイプのJOINおよびORおよびANDを含む条件のクエリ：

注記

デフォルトでは、非等条件は同じテーブルのカラムを使用する限りサポートされています。 たとえば、t1.a = t2.key AND t1.b > 0 AND t2.b > t2.c、t1.b > 0はt1だけのカラムを使用し、t2.b > t2.cはt2だけのカラムを使用します。 ただし、t1.a = t2.key AND t1.b > t2.keyのような条件の実験的なサポートを試すことができます。詳細は以下のセクションを確認してください。

結果：

異なるテーブルからのカラムに対する不等条件を持つJOIN​

ClickHouseでは、現時点でALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOINを不等条件でサポートしています。これに加えて等号条件がサポートされます。不等条件は、hashおよびgrace_hash結合アルゴリズムでのみサポートされています。join_use_nullsでは不等条件はサポートされていません。

例

テーブルt1:

テーブルt2:

JOINキーのNULL値​

NULLは任意の値、自己すら等しくありません。これは、JOINキーが一方のテーブルにNULLの値を持っている場合、他方のテーブルのNULL値と一致しないことを意味します。

例

テーブルA:

テーブルB:

JOINキーにNULL値があるため、テーブルAのCharlieの行とテーブルBのスコア88の行は結果に含まれていないことに注意してください。

NULL値を一致させたい場合は、isNotDistinctFrom関数を使用してJOINキーを比較してください。

ASOF JOINの使用法​

ASOF JOINは、正確な一致がないレコードを結合する必要がある場合に便利です。

このアルゴリズムには、テーブル内の特別なカラムが必要です。このカラムは：

  • 順序されたシーケンスを含む必要があります。
  • 以下のいずれかのタイプであることができます： Int, UInt、Float、Date、DateTime、Decimal。
  • hash結合アルゴリズムの場合、JOIN句における唯一のカラムであってはなりません。

構文 ASOF JOIN ... ON：

等号条件を任意の数使用できますが、最も近い一致条件は1つだけ使用できます。たとえば、SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.tのようにします。

最も近い一致のためにサポートされている条件：>、>=、<、<=。

構文 ASOF JOIN ... USING：

ASOF JOINは、等号のためにequi_columnXを使用し、最も近い一致のためにasof_columnを使用します。この場合、table_1.asof_column >= table_2.asof_column条件が必要です。asof_columnカラムは常にUSING句の最後にあります。

以下のテーブルを考えてみましょう：

table_1 table_2 event | ev_time | user_id event | ev_time | user_id ----------|---------|---------- ----------|---------|---------- ... ... event_1_1 | 12:00 | 42 event_2_1 | 11:59 | 42 ... event_2_2 | 12:30 | 42 event_1_2 | 13:00 | 42 event_2_3 | 13:00 | 42 ... ...

ASOF JOINは、table_1のユーザーイベントのタイムスタンプを取り、最も近い一致条件に該当するtable_1のタイムスタンプに最も近いtable_2のイベントを見つけます。等しいタイムスタンプの値は、利用可能であれば最も近いものです。ここで、user_idカラムは等号のための結合に使用でき、ev_timeカラムは最も近い一致のために使用できます。この例では、event_1_1はevent_2_1と結合され、event_1_2はevent_2_3と結合できますが、event_2_2は結合できません。

注記

ASOF JOINは、hashおよびfull_sorting_merge結合アルゴリズムでのみサポートされています。 Joinテーブルエンジンではサポートされていません。

PASTE JOINの使用法​

PASTE JOINの結果は、左側のサブクエリからのすべてのカラムの後に、右側のサブクエリからのすべてのカラムを含むテーブルです。 行は、元のテーブルにおける位置に基づいて一致します（行の順序は定義する必要があります）。 サブクエリが異なる行数を返す場合、余分な行は切り取られます。

例：

注意: この場合、結果は並行読み取りされると非決定的な場合があります。例：

分散JOIN​

分散テーブルに関連するJOINを実行する方法は2つあります：

  • 通常のJOINを使用すると、クエリはリモートサーバーに送信されます。サブクエリは、右側のテーブルを作成するために各サーバーで実行され、そのテーブルとJOINが実行されます。言い換えれば、右側のテーブルは各サーバーでそれぞれ形成されます。
  • GLOBAL ... JOINを使用すると、最初にリクエスタサーバーがサブクエリを実行して右側のテーブルを計算します。この一時テーブルは各リモートサーバーに渡され、送信された一時データを使用してクエリが実行されます。

GLOBALを使用する際には注意が必要です。詳細については、分散サブクエリセクションを参照してください。

暗黙的型変換​

INNER JOIN、LEFT JOIN、RIGHT JOIN、およびFULL JOINクエリは、「結合キー」に対して暗黙的な型変換をサポートしています。ただし、左側と右側のテーブルの結合キーを単一のタイプに変換できない場合、クエリは実行できません（たとえば、UInt64とInt64、またはStringとInt32の両方のすべての値を保持できるデータ型は存在しません）。

例

テーブルt_1を考えます：

およびテーブルt_2：

クエリ

は次のセットを返します：

使用の推奨​

空またはNULLセルの処理​

テーブルを結合すると、空のセルが発生する場合があります。join_use_nulls設定は、ClickHouseがこれらのセルをどのように埋めるかを定義します。

JOINキーがNullableフィールドである場合、キーのうち少なくとも1つがNULL値を持つ行は結合されません。

構文​

USINGで指定されたカラムは、両方のサブクエリで同じ名前を持っている必要があり、他のカラムは異なる名前でなければなりません。エイリアスを使用してサブクエリ内のカラムの名前を変更できます。

USING句は、結合に複数のカラムを指定し、これによりこれらのカラムの等式を確立します。カラムのリストは括弧なしで設定されます。より複雑な結合条件はサポートされていません。

構文の制限​

単一のSELECTクエリ内で複数のJOIN句がある場合：

  • *を介してすべてのカラムを取得することは、テーブルが結合されている場合のみ利用できます。サブクエリは含まれません。
  • PREWHERE句は利用できません。
  • USING句は利用できません。

ON 、 WHERE 、および GROUP BY 句の場合：

  • ON、WHERE、およびGROUP BY句で任意の表現を使用できませんが、SELECT句で表現を定義し、エイリアスを介してそれをこれらの句で使用できます。

パフォーマンス​

JOINを実行するとき、他のクエリステージに対する実行順序の最適化は行われません。JOIN（右側のテーブルの検索）は、WHEREでのフィルタリングおよび集約の前に実行されます。

同じJOINでクエリが実行されるたびに、サブクエリは再度実行されます。結果はキャッシュされません。これを避けるには、常にRAM内にあるJOIN用の準備された配列である特別なJoinテーブルエンジンを使用してください。

場合によっては、JOINではなく、INを使用する方が効率的です。

次元テーブル（これらは、広告キャンペーンの名前などの次元特性を含む比較的小さなテーブル）との結合にJOINが必要な場合、右側のテーブルがすべてのクエリに対して再アクセスされるため、JOINはそれほど便利ではないかもしれません。そのような場合には、JOINの代わりに「辞書」機能を使用することをお勧めします。詳細については、辞書セクションを参照してください。

メモリ制限​

デフォルトでは、ClickHouseはハッシュJOINアルゴリズムを使用します。ClickHouseは右側のテーブルを取り、それに対してRAM内にハッシュテーブルを作成します。join_algorithm = 'auto'が有効になっている場合、一部のメモリ消費の閾値を超えると、ClickHouseはマージ結合アルゴリズムに戻ります。JOINアルゴリズムの説明については、join_algorithm設定を参照してください。

JOIN操作のメモリ消費を制限する必要がある場合は、次の設定を使用してください：

これらのいずれかの制限に達すると、ClickHouseはjoin_overflow_mode設定に従って動作します。

例​

例：