JOIN句
JOIN
句は、1つまたは複数のテーブルからのカラムを、各テーブルに共通する値を使用して結合することで新しいテーブルを生成します。これは、SQLをサポートするデータベースにおける一般的な操作であり、関係代数の結合に対応します。1つのテーブルの結合の特別なケースは「自己結合」と呼ばれます。
構文
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で利用可能な追加の結合タイプは次のとおりです:
タイプ | 説明 |
---|---|
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
の厳密さでのみサポートされています(SEMI
、ANTI
、ANY
、およびASOF
はサポートされていません)。
設定
デフォルトの結合タイプは、join_default_strictness
設定を使用してオーバーライドできます。
ANY JOIN
操作に対するClickHouseサーバーの動作は、any_join_distinct_right_table_keys
設定に依存します。
参照
join_algorithm
join_any_take_last_row
join_use_nulls
partial_merge_join_rows_in_right_blocks
join_on_disk_max_files_to_merge
any_join_distinct_right_table_keys
cross_to_inner_join_rewrite
設定を使用して、ClickHouseがCROSS JOIN
をINNER JOIN
として書き換えられなかった場合の動作を定義します。デフォルト値は1
で、結合は続行されますが遅くなります。cross_to_inner_join_rewrite
を0
に設定するとエラーが発生し、2
に設定するとすべてのカンマ/クロス結合の書き換えを強制します。値が2
のときに書き換えが失敗した場合、「WHERE
セクションを簡素化してください」とのエラーメッセージが表示されます。
ONセクションの条件
ON
セクションには、AND
およびOR
演算子を使用して結合された複数の条件を含めることができます。結合キーを指定する条件は以下を満たす必要があります:
- 左テーブルと右テーブルの両方を参照すること
- 等号演算子を使用すること
その他の条件は他の論理演算子を使用できますが、クエリの左テーブルまたは右テーブルのいずれかを参照する必要があります。
行は、全体の複雑な条件が満たされた場合に結合されます。条件が満たされない場合、行はJOIN
タイプに応じて結果に含まれる可能性があります。WHERE
セクションに同じ条件が配置され、それが満たされない場合、行は結果から常にフィルタリングされます。
ON
句内のOR
演算子はハッシュ結合アルゴリズムを使用して機能します。JOIN
のために結合キーを持つ各OR
引数のために、別のハッシュテーブルが作成されるため、メモリ消費量とクエリ実行時間はON
句のOR
式の数が増えると線形に増加します。
異なるテーブルのカラムを参照する条件の場合、現在は等号演算子(=
)のみがサポートされています。
例
table_1
とtable_2
を考えます:
1つの結合キー条件とtable_2
に対する追加条件を使用したクエリ:
結果にC
という名前の行と空のテキストカラムが含まれていることに注意してください。これはOUTER
型の結合が使用されているため、結果に含まれています。
INNER
型の結合と複数の条件を使用したクエリ:
結果:
INNER
型の結合とOR
条件を使用したクエリ:
結果:
INNER
型の結合と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
:
A
テーブルのCharlie
行とB
テーブルのスコア88行が結果に含まれていないことに注意してください。これはJOIN
キーにNULL
値があるためです。
NULL
値を一致させたい場合は、isNotDistinctFrom
関数を使用してJOIN
キーを比較します。
ASOF JOINの使用法
ASOF JOIN
は、正確な一致がないレコードを結合する必要がある場合に役立ちます。
この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
条件で結合します。USING
句の最後のカラムは常にasof_column
です。
例えば、以下のテーブルを考えます:
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
を使用する場合、クエリはリモートサーバーに送信されます。サブクエリは各サーバーで実行され、右テーブルが作成され、そのテーブルで結合が実行されます。言い換えれば、右テーブルは各サーバーで個別に形成されます。 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
句は、等式を確立するカラムを1つ以上指定します。カラムのリストは括弧なしで設定されます。より複雑な結合条件はサポートされていません。
構文の制限
単一のSELECT
クエリ内で複数のJOIN
句がある場合:
- すべてのカラムを
*
で取得するのは、テーブルが結合されている場合のみ可能で、サブクエリの場合は不可能です。 PREWHERE
句は使用できません。USING
句は使用できません。
ON
、WHERE
、およびGROUP BY
句について:
ON
、WHERE
、およびGROUP BY
句内で任意の式を使用することはできませんが、SELECT
句内で式を定義し、その後、エイリアスを介してこれらの句で使用することができます。
パフォーマンス
JOIN
を実行する際、クエリの他のステージに関する実行順序の最適化は行われません。結合(右テーブルの検索)は、WHERE
でのフィルタリング前と集約前に実行されます。
同じJOIN
でクエリを実行するたびに、サブクエリが再実行されます。これは結果がキャッシュされないためです。これを避けるためには、常にRAM内に準備された結合用の配列である特別なJoinテーブルエンジンを使用することができます。
場合によっては、JOIN
の代わりにINを使用する方が効率的です。
次元テーブルとの結合にJOINが必要な場合(これは、広告キャンペーンの名前などの次元プロパティを含む比較的小さなテーブルです)、右テーブルが各クエリのために再アクセスされるため、JOINは非常に便利ではありません。そのような場合には、JOINの代わりに「辞書」機能を使用する必要があります。詳細については、辞書セクションを参照してください。
メモリ制限
デフォルトでは、ClickHouseはハッシュ結合アルゴリズムを使用します。ClickHouseは右テーブルを取得し、RAM内にそのためのハッシュテーブルを作成します。join_algorithm = 'auto'
が有効な場合、メモリ消費の閾値を超えると、ClickHouseはマージ結合アルゴリズムにフォールバックします。JOIN
アルゴリズムの説明については、join_algorithm設定を参照してください。
JOIN
操作のメモリ消費を制限する必要がある場合、次の設定を使用します:
- max_rows_in_join — ハッシュテーブルにおける行数の制限。
- max_bytes_in_join — ハッシュテーブルのサイズの制限。
これらの制限のいずれかに達すると、ClickHouseはjoin_overflow_mode設定に従って挙動します。
例
例:
関連情報
- ブログ: ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Part 1
- ブログ: ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 2
- ブログ: ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 3
- ブログ: ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 4