JOIN 句は、共通する値を使って 1 つ以上のテーブルのカラムを結合し、新しいテーブルを生成します。これは SQL をサポートするデータベースで一般的な操作であり、関係代数における join に対応します。1 つのテーブルをそれ自身と結合する特殊なケースは、しばしば “自己結合” と呼ばれます。
構文
ON 句の式と USING 句のカラムは、「結合キー」と呼ばれます。特に明記しない限り、JOIN は「結合キー」が一致する行から 直積 を生成するため、結果の行数が元のテーブルより大幅に多くなることがあります。
サポートされる JOIN の種類
| Type | Description |
|---|---|
INNER JOIN | 一致する行のみが返されます。 |
LEFT OUTER JOIN | 一致する行に加えて、左テーブルで一致しない行も返されます。 |
RIGHT OUTER JOIN | 一致する行に加えて、右テーブルで一致しない行も返されます。 |
FULL OUTER JOIN | 一致する行に加えて、両方のテーブルで一致しない行も返されます。 |
CROSS JOIN | テーブル全体のデカルト積を生成し、結合キーは指定しません。 |
NATURAL JOIN | 両方のテーブルで同じ名前を持つすべてのカラムを使って自動的に結合し、共通カラムは結果に 1 回だけ現れます。INNER (デフォルト) 、LEFT、RIGHT、FULL の各バリアントをサポートします。カラムのリストが自動的に導出される JOIN ... USING (col1, col2, ...) と同等です。 |
- 種類を指定しない
JOINはINNERを意味します。 - キーワード
OUTERは省略できます。 CROSS JOINの別構文として、FROMclause で複数のテーブルをカンマ区切りで指定できます。NATURAL JOINで一致するカラムがない場合は、CROSS JOINのように動作します。
| Type | Description |
|---|---|
LEFT SEMI JOIN, RIGHT SEMI JOIN | デカルト積を生成せずに、“結合キー” に対する許可リストとして機能します。 |
LEFT ANTI JOIN, RIGHT ANTI JOIN | デカルト積を生成せずに、“結合キー” に対する拒否リストとして機能します。 |
LEFT ANY JOIN, RIGHT ANY JOIN, INNER ANY JOIN | 標準の JOIN 種類に対して、デカルト積を部分的に (LEFT と RIGHT の反対側) または完全に (INNER と FULL) 無効にします。 |
ASOF JOIN, LEFT ASOF JOIN | 完全一致ではない条件で数列を結合します。ASOF JOIN の使い方は以下で説明します。 |
PASTE JOIN | 2 つのテーブルを水平方向に連結します。 |
join_algorithm が
partial_merge に設定されている場合、RIGHT JOIN と FULL JOIN は ALL strictness でのみサポートされます (SEMI、ANTI、ANY、ASOF はサポートされません) 。設定
join_default_strictness 設定で変更できます。
ANY JOIN 操作における ClickHouseサーバーの動作は、any_join_distinct_right_table_keys 設定に依存します。
関連項目
join_algorithmjoin_any_take_last_rowjoin_use_nullspartial_merge_join_rows_in_right_blocksjoin_on_disk_max_files_to_mergeany_join_distinct_right_table_keys
CROSS JOIN を INNER JOIN に書き換えできない場合の動作は、cross_to_inner_join_rewrite 設定で定義します。デフォルト値は 1 で、この場合 JOIN は継続されますが、処理は遅くなります。エラーを発生させたい場合は cross_to_inner_join_rewrite を 0 に設定し、cross join を実行せず、代わりにすべての comma/cross join の書き換えを強制したい場合は 2 に設定します。値が 2 のときに書き換えに失敗すると、“Please, try to simplify WHERE section” というエラーメッセージが表示されます。
ON セクションの条件
ON セクションには、AND 演算子および OR 演算子で組み合わせた複数の条件を含めることができます。結合キーを指定する条件は、次を満たす必要があります。
- 左右両方のテーブルを参照していること
- 等価演算子を使用していること
JOIN の種類によっては行が結果に含まれることがあります。同じ条件を WHERE セクションに置いた場合、それらの条件が満たされなければ、行は常に結果から除外される点に注意してください。
ON 句内の OR 演算子は、ハッシュ結合アルゴリズムを使って動作します。JOIN の結合キーを含む OR の各引数ごとに個別のハッシュテーブルが作成されるため、メモリ使用量とクエリ実行時間は、ON 句内の OR 式の数が増えるのに応じて線形に増加します。
条件が異なるテーブルのカラムを参照する場合、現時点では等価演算子 (
=) のみがサポートされています。table_1 と table_2 を考えます。
table_2 に対する追加条件を含むクエリ:
Query
C で、text カラムが空の行も含まれていることに注意してください。これは、OUTER 型の JOIN を使用しているためです。
Response
INNER 型の JOIN と複数の条件を含むクエリ:
Query
Response
INNER で、条件に OR を使ったクエリ：
Query
Response
OR と AND 条件を含む INNER JOIN のクエリ:”
デフォルトでは、非等価条件は、同じテーブルのカラムだけを使用している場合にサポートされます。
たとえば、
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 のような条件に対する実験的サポートを試すこともできます。詳しくは以下のセクションを参照してください。Query
Response
異なるテーブルのカラムに対する不等式条件付き JOIN
ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN をサポートしています。不等式条件をサポートするのは、hash および grace_hash JOIN アルゴリズムのみです。不等式条件は join_use_nulls と併用できません。
例
テーブル t1:
t2
JOIN の結合キーにおける NULL 値
NULL は、自身を含め、どの値とも等しくありません。つまり、あるテーブルの JOIN の結合キーが NULL 値であっても、もう一方のテーブルの NULL 値とは一致しません。
例
テーブル A:
B：
A テーブルの Charlie の行と、B テーブルのスコア 88 の行は、JOIN キーが NULL のため、結果に含まれていないことに注意してください。
NULL 値同士を一致させたい場合は、isNotDistinctFrom 関数を使用して JOIN キーを比較してください。
ASOF JOIN の使い方
ASOF JOIN は、完全に一致するレコードがない場合に、それらを結合する際に役立ちます。
この JOIN アルゴリズムでは、テーブル内に特別なカラムが必要です。このカラムは次の条件を満たしている必要があります。
- 順序付けされた数列を含んでいる必要があります。
- 次のいずれかの型である必要があります: Int, UInt, Float, Date, DateTime, Decimal。
hashJOIN アルゴリズムでは、JOIN句内で唯一のカラムにすることはできません。
ASOF JOIN ... ON:
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 を使用し、table_1.asof_column >= table_2.asof_column 条件のもとで最も近い一致による結合に asof_column を使用します。asof_column カラムは、USING 句では常に最後になります。
たとえば、次のテーブルを考えます。
ASOF JOIN では、table_1 のユーザーイベントのタイムスタンプを基に、最も近い一致条件に対応する table_1 のイベントのタイムスタンプに最も近いタイムスタンプを持つ table_2 のイベントを見つけることができます。利用可能であれば、同じタイムスタンプ値が最も近いものとして扱われます。ここでは、等値で JOIN するために user_id カラムを使用でき、最も近い一致で JOIN するために ev_time カラムを使用できます。この例では、event_1_1 は event_2_1 と JOIN でき、event_1_2 は event_2_3 と JOIN できますが、event_2_2 は JOIN できません。
ASOF JOIN は、hash および full_sorting_merge JOIN アルゴリズムでのみサポートされています。
Join テーブルエンジンではサポートされていません。PASTE JOIN の使用法
PASTE JOIN の結果は、左側のサブクエリのすべてのカラムに、右側のサブクエリのすべてのカラムを続けて含むテーブルです。
行は元のテーブル内での位置に基づいて対応付けられます (行の順序が定義されている必要があります) 。
サブクエリが返す行数が異なる場合、余分な行は切り捨てられます。
例:
分散 JOIN
- 通常の
JOINを使用する場合、クエリはリモートサーバーに送信されます。右側のテーブルを作成するために、各サーバー上でサブクエリが実行され、そのテーブルと JOIN が行われます。つまり、右側のテーブルは各サーバーごとに個別に作成されます。 GLOBAL ... JOINを使用する場合、まずリクエスト元のサーバーがサブクエリを実行して JOIN の片側を計算し、その結果を一時テーブルに格納します。次に、この一時テーブルが各リモートサーバーに渡され、転送された一時データを使ってそれらのサーバー上でクエリが実行されます。LEFTおよびINNERJOIN では、右側のテーブルがサブクエリとして計算されます。RIGHTJOIN では、保持されるのが右側のテーブルであり、分片から読み取る必要があるため、代わりに左側のテーブルが計算されます。
GLOBAL を使用する際は注意してください。詳しくは、分散サブクエリ のセクションを参照してください。
暗黙的な型変換
INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN のクエリでは、「結合キー」に対する暗黙的な型変換がサポートされています。ただし、左側と右側のテーブルの結合キーを単一の型に変換できない場合、クエリは実行できません (たとえば、UInt64 と Int64 の両方の値、あるいは String と Int32 の両方の値を保持できるデータ型は存在しません) 。
例
テーブル t_1 を考えます:
t_2:
利用上の推奨事項
空またはNULLのセルの処理
JOIN のキーが Nullable のフィールドである場合、少なくとも1つのキーの値が NULL である行は結合されません。
構文
USING で指定するカラムは、両方のサブクエリで同じ名前である必要があり、それ以外のカラムは異なる名前である必要があります。サブクエリ内のカラム名を変更するには、別名を使用できます。
USING 句では、結合に使用する1つ以上のカラムを指定します。これにより、それらのカラムが等しいものとして扱われます。カラムのリストは括弧を付けずに指定します。より複雑な結合条件はサポートされていません。
構文上の制限事項
SELECT クエリ内で複数の JOIN 句を使用する場合:
*によるすべてのカラムの取得は、サブクエリではなく、テーブルを結合している場合にのみ使用できます。PREWHERE句は使用できません。USING句は使用できません。
ON、WHERE、GROUP BY 句について:
ON、WHERE、GROUP BY句では任意の式は使用できませんが、SELECT句で式を定義し、その後エイリアスを介してこれらの句で使用できます。
パフォーマンス
JOIN を実行する際、クエリの他の処理段階との関係において、実行順序の最適化は行われません。JOIN (右側のテーブルの検索) は、WHERE によるフィルタリングより前、さらに集約より前に実行されます。
同じ JOIN を使ってクエリを実行するたびに、結果がキャッシュされないため、サブクエリは毎回再実行されます。これを避けるには、結合用にあらかじめ用意された配列で、常に RAM 上に置かれる特別な Join テーブルエンジンを使用してください。
場合によっては、JOIN の代わりに IN を使用するほうが効率的です。
ディメンションテーブル (広告キャンペーン名のようなディメンション属性を含む比較的小さなテーブル) との結合に JOIN が必要な場合、クエリごとに右側のテーブルへ再アクセスする必要があるため、JOIN はあまり適していないことがあります。そのようなケースでは、JOIN の代わりに使用すべき “dictionaries” 機能があります。詳細は Dictionaries セクションを参照してください。
メモリ制限
join_algorithm = 'auto' が有効な場合、メモリ使用量があるしきい値を超えると、ClickHouse は merge 結合アルゴリズムにフォールバックします。JOIN アルゴリズムの説明については、join_algorithm 設定を参照してください。
JOIN 操作のメモリ使用量を制限する必要がある場合は、次の設定を使用します。
- max_rows_in_join — ハッシュテーブル内の行数を制限します。
- max_bytes_in_join — ハッシュテーブルのサイズを制限します。