JOIN

JOIN 句は、共通する値を使って 1 つ以上のテーブルのカラムを結合し、新しいテーブルを生成します。これは SQL をサポートするデータベースで一般的な操作であり、関係代数における join に対応します。1 つのテーブルをそれ自身と結合する特殊なケースは、しばしば “自己結合” と呼ばれます。構文

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ALL|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

ON 句の式と USING 句のカラムは、「結合キー」と呼ばれます。特に明記しない限り、JOIN は「結合キー」が一致する行からデカルト積を生成するため、結果の行数が元のテーブルより大幅に多くなることがあります。

サポートされる JOIN の種類

標準的な SQL 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 の別構文として、FROM 句で複数のテーブルをカンマ区切りで指定できます。
NATURAL JOIN で一致するカラムがない場合は、CROSS JOIN のように動作します。

ClickHouse では、次の追加の 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 つのテーブルを水平方向に連結します。

analyzer を使用している場合に semi_join_include_columns_from_both_sides または anti_join_include_columns_from_both_sides 設定を無効にすると、該当する結合では、JOIN 結果の生成後に解決される式から参照できるのは保持される側のみになります。

LEFT SEMI JOIN と LEFT ANTI JOIN では、左側のカラムのみが公開されます。
RIGHT SEMI JOIN と RIGHT ANTI JOIN では、右側のカラムのみが公開されます。
これは SELECT、PREWHERE、WHERE、GROUP BY、HAVING、QUALIFY、ORDER BY、LIMIT BY などの句に影響し、t1.* のような修飾付きワイルドカードも対象となります。
同じ JOIN の ON 式からは、引き続き両側を参照できます。

これらの設定が有効な場合 (デフォルト) 、ClickHouse は従来の動作を維持します。つまり両側に引き続きアクセスでき、SELECT * は両方のテーブルのカラムに展開されます。

join_algorithm が partial_merge に設定されている場合、RIGHT JOIN と FULL JOIN は ALL strictness でのみサポートされます (SEMI、ANTI、ANY、ASOF はサポートされません) 。

LATERAL JOIN

JOIN LATERAL を使用すると、結合の右側にあるサブクエリから、その左側にあるテーブル式のカラムを参照できます。サブクエリは、参照している左側のカラム値の異なる組み合わせごとに評価され、その結果は同じ組み合わせを持つ左側のすべての行に結合されます：

SELECT ...
FROM <left_table>
[INNER|LEFT] JOIN LATERAL (SELECT ... WHERE <expr referencing left_table>) AS <alias> ON true

他の INNER または LEFT JOIN と同様に、ON true 述語は必須です。省略すると構文エラーになります。この機能は実験的であり、デフォルトでは無効になっています。有効にするには allow_experimental_lateral_join 設定を使用します。現時点でサポートされているのは以下の機能のみで、それ以外はすべてエラーとして拒否されます。

サポートされるのは INNER JOIN LATERAL と LEFT JOIN LATERAL のみです。RIGHT、FULL、PASTE、NATURAL の結合はサポートされていません。また、LATERAL を CROSS 結合やカンマ結合と組み合わせることは一切できません。
サポートされる strictness はデフォルトの ALL のみです。ANY、SEMI、ANTI、ASOF はサポートされていません。
ON true 以外の結合述語は使用できません (ON 1 も使用可能です) 。USING はサポートされておらず、述語を省略することもできません。両側を関連付けるフィルタは、ラテラルサブクエリの WHERE 句に記述してください。
GLOBAL および LOCAL 結合修飾子はサポートされていません。
ラテラルサブクエリは、左側のカラムを少なくとも 1 つ参照する必要があります。相関のないサブクエリには通常の結合を使用してください。
ラテラルサブクエリは、左側の行ごとに 1 回ではなく、参照する左側カラムの値の組み合わせごとに 1 回評価されます。そのため、rand や generateUUIDv4 のようにクエリ内で非決定論的な関数や、generateRandom のようにランダムな行を生成するテーブル関数を含めることはできません。 now のようにクエリ内で値が一定の関数は使用できます。
ラテラルテーブル式としてサポートされるのはサブクエリのみです。PostgreSQL のテーブルソース形式である LATERAL unnest(...) および CROSS JOIN UNNEST(...) はサポートされていません。代わりに ARRAY JOIN 句を使用してください。
ラテラルサブクエリの GROUP BY と ORDER BY は、すべての評価結果に対してまとめて 1 回だけ実行されます。そのため、 max_rows_to_group_by、max_rows_to_sort、max_bytes_to_sort の制限では、1 回分ではなくすべての評価の行がカウントされます。これらの制限は throw オーバーフローモードでのみサポートされ、any と break は拒否されます。
すべての評価結果の行は、単一の結合によって左側の行と照合されます。他のハッシュ結合と同様に、 max_rows_in_join と max_bytes_in_join はこの結合のうちメモリ上に保持される側を制限し、どちらの側を保持するかはプランナーが決定します。デフォルト設定 (correlated_subqueries_use_in_memory_buffer = 1) では、ラテラルサブクエリを評価する前に左側の行をすべて読み込む必要があるため、JOIN LATERAL の左側が保持されます。それ以外の場合は、すべての評価結果をまとめたものが保持されることがあります。これらの制限は常に join_overflow_mode が throw であるものとして適用されます。break では、無関係な左側の行が結合によって警告なく破棄されてしまうためです。

例

SELECT u.id, o.total
FROM users AS u
LEFT JOIN LATERAL
(
    SELECT total
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY total DESC
    LIMIT 1
) AS o ON true
SETTINGS allow_experimental_lateral_join = 1;

設定

デフォルトの JOIN の種類は、join_default_strictness 設定で変更できます。 ANY JOIN 操作における ClickHouseサーバーの動作は、any_join_distinct_right_table_keys 設定に依存します。 関連項目

ClickHouse が 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 式の数が増えるのに応じて線形に増加します。

条件を結合キーとして扱わせるのは等価演算子 (=) だけです。異なるテーブルのカラム間でその他の演算子を使用することもできますが、等価条件が 1 つもない場合、結合処理ではすべての行の組み合わせを調べる必要があるため、大幅に遅くなります。詳しくは任意の ON 条件による JOIN を参照してください。

例 table_1 と table_2 を考えます。

┌─Id─┬─name─┐     ┌─Id─┬─text───────────┬─scores─┐
│  1 │ A    │     │  1 │ Text A         │     10 │
│  2 │ B    │     │  1 │ Another text A │     12 │
│  3 │ C    │     │  2 │ Text B         │     15 │
└────┴──────┘     └────┴────────────────┴────────┘

1 つの結合キー条件と、table_2 に対する追加条件を含むクエリ:

Query

SELECT name, text FROM table_1 LEFT OUTER JOIN table_2
    ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text');

結果には、名前が C で、text カラムが空の行も含まれていることに注意してください。これは、OUTER 型の JOIN を使用しているためです。

Response

┌─name─┬─text───┐
│ A    │ Text A │
│ B    │ Text B │
│ C    │        │
└──────┴────────┘

INNER 型の JOIN と複数の条件を含むクエリ:

Query

SELECT name, text, scores FROM table_1 INNER JOIN table_2
    ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text');

Response

┌─name─┬─text───┬─scores─┐
│ B    │ Text B │     15 │
└──────┴────────┴────────┘

join の種類が INNER で、条件に OR を使ったクエリ：

Query

CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;

CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;

INSERT INTO t1 SELECT number as a, -a as b from numbers(5);

INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key;

Response

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 1 │ -1 │   1 │
│ 2 │ -2 │   2 │
│ 3 │ -3 │   3 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

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

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3;

Response

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 2 │ -2 │   2 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

異なるテーブルのカラムに対する不等式条件付き JOIN

ClickHouse は、等価条件に加えて不等式条件を含む ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN をサポートしています。 ON 句に不等式に加えて 2 つのテーブル間の等価条件が含まれている場合、その等価条件が結合キーとなり、不等式はそれによってマッチした行に対して評価されます。このような混在した条件を実行できるのは、hash、parallel_hash、および grace_hash JOIN アルゴリズムのみです。 ON 句に 2 つのテーブル間の等価条件が含まれていない場合、マッチに使用する結合キーは存在しません。このような JOIN は、条件が不等式のペアである場合は ie_join によって実行され、それ以外の場合は block nested loop join として実行されます。 JOIN 中に評価される条件には arrayJoin を含めることはできません。このような条件では行数を維持する必要があるためです。代わりにサブクエリ内で ARRAY JOIN を使用してください。例テーブル t1:

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ a    │ 1 │ 1 │ 2 │
│ key1 │ b    │ 2 │ 3 │ 2 │
│ key1 │ c    │ 3 │ 2 │ 1 │
│ key1 │ d    │ 4 │ 7 │ 2 │
│ key1 │ e    │ 5 │ 5 │ 5 │
│ key2 │ a2   │ 1 │ 1 │ 1 │
│ key4 │ f    │ 2 │ 3 │ 4 │
└──────┴──────┴───┴───┴───┘

テーブル t2

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ A    │ 1 │ 2 │ 1 │
│ key1 │ B    │ 2 │ 1 │ 2 │
│ key1 │ C    │ 3 │ 4 │ 5 │
│ key1 │ D    │ 4 │ 1 │ 6 │
│ key3 │ a3   │ 1 │ 1 │ 1 │
│ key4 │ F    │ 1 │ 1 │ 1 │
└──────┴──────┴───┴───┴───┘

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.key = t2.key AND (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);

key1    a    1    1    2    key1    B    2    1    2
key1    a    1    1    2    key1    C    3    4    5
key1    a    1    1    2    key1    D    4    1    6
key1    b    2    3    2    key1    C    3    4    5
key1    b    2    3    2    key1    D    4    1    6
key1    c    3    2    1    key1    D    4    1    6
key1    d    4    7    2            0    0    \N
key1    e    5    5    5            0    0    \N
key2    a2    1    1    1            0    0    \N
key4    f    2    3    4            0    0    \N

任意の ON 条件による JOIN

ON 句には、範囲チェック、算術比較、スカラー関数の呼び出しなど、両方のテーブルのカラムを対象とした任意のブール式を指定できます。2 つのテーブル間の等値比較が含まれていない場合、行の照合に使う結合キーは存在しません。不等式条件の組は、join_algorithm に ie_join が含まれていれば (デフォルトでは含まれています)、ie_join によって実行されます。それ以外の条件は block nested loop join として実行されます。この場合、右テーブルがマテリアライズされ、すべての行の組に対して条件が評価されます。ALL INNER JOIN は、条件をフィルタとする CROSS JOIN と同等の形式になります。 block nested loop join は、ASOF JOIN、PASTE JOIN、ANY FULL JOIN を除くすべての結合種別と厳密性をサポートします。これは join_algorithm に指定できる値ではなく、他のどのアルゴリズムでも条件を実行できない場合にのみ使われる最後の手段です。EXPLAIN の出力では BlockNestedLoopJoin ステップとして表示されます。allow_block_nested_loop_join が無効の場合、これを必要とするクエリは INVALID_JOIN_ON_EXPRESSION エラーで拒否されます。例

Query

CREATE TABLE orders (id UInt32, amount UInt32) ENGINE = Memory;
CREATE TABLE discounts (min_amount UInt32, max_amount UInt32, pct UInt32) ENGINE = Memory;

INSERT INTO orders VALUES (1, 50), (2, 150), (3, 300);
INSERT INTO discounts VALUES (100, 199, 5), (200, 1000, 10);

SELECT o.id, o.amount, d.pct
FROM orders AS o
LEFT JOIN discounts AS d ON o.amount BETWEEN d.min_amount AND d.max_amount
ORDER BY o.id;

Response

┌─id─┬─amount─┬─pct─┐
│  1 │     50 │   0 │
│  2 │    150 │   5 │
│  3 │    300 │  10 │
└────┴────────┴─────┘

どの行にもマッチしなかった行は、他の結合と同様に join_use_nulls に従ってパディングされます。上記の例ではデフォルト値で埋められ、この設定が有効な場合は NULL で埋められます。 厳密性 ANY と SEMI は通常、同じ結合キーを持つ行のグループごとに 1 行を保持します。ここではそのようなグループが存在しないため、結合を駆動する側のテーブルの各行につき 1 行を保持します。LEFT ANY と LEFT SEMI は左側の各行を最大 1 回、RIGHT ANY と RIGHT SEMI は右側の各行を最大 1 回出力します。ANY INNER は両側を同時に制限します。つまり、どちらのテーブルの行も最大 1 回しか使用されないため、結果の行数は小さい方のテーブルの行数以下になります。そうした結果がどのペアで構成されるかは、ANY が意味するとおり任意であり、同じクエリを 2 回実行しても異なる場合があります。ANY INNER では、これが行数にも及びます。両側の行は、調べられた順序に従って貪欲にペアにされるため、ペアにならずに残る行の数が変わる可能性があります。そのため、結合キーのない ANY INNER 結合に対する count() の結果には再現性がなく、スレッド数や右テーブルの物理的な順序に左右されます。 パフォーマンス すべての行のペアが調べられるため、処理量は 2 つのテーブルの行数の和ではなく積に比例して増加します。そのため、block nested loop join は同じデータに対するハッシュ結合よりも桁違いにコストが高く、テーブルが大きくなるほどその差は広がります。ON 句に少なくとも 1 つの等値比較を含む形でクエリを記述できる場合は、そのように記述してください。 LEFT ANY、ANY INNER、LEFT SEMI、LEFT ANTI 結合は、左側の行に対して最初にマッチする行が見つかった時点で右テーブルのスキャンを停止するため、通常は対応する ALL 結合よりも低コストです。一方、右側駆動の対応する結合 (RIGHT ANY、RIGHT SEMI、RIGHT ANTI) は、どの右側の行がマッチしたかによって結果が決まるため、すべてのペアを調べます。join_any_take_last_row はここでは効果がありません。結合キーがない場合、最後の行を取り出す対象となるマッチ行のグループが存在しないためです。メモリ使用量はマテリアライズされた右テーブルのサイズで上限が決まり、結果のサイズに応じて増加することはありません。右テーブルには、他の JOIN アルゴリズムと同じ設定が適用されます (Memory limitations を参照) 。具体的には、max_rows_in_join、max_bytes_in_join、join_overflow_mode で右テーブルのサイズを制限でき、max_bytes_before_external_join を設定するとディスクへのスピルが行われます。

JOIN の結合キーにおける NULL 値と NaN 値

NULL は、自身を含め、どの値とも等しくありません。つまり、あるテーブルの JOIN の結合キーが NULL 値であっても、もう一方のテーブルの NULL 値とは一致しません。例テーブル A:

┌───id─┬─name────┐
│    1 │ Alice   │
│    2 │ Bob     │
│ ᴺᵁᴸᴸ │ Charlie │
└──────┴─────────┘

テーブル B：

┌───id─┬─score─┐
│    1 │    90 │
│    3 │    85 │
│ ᴺᵁᴸᴸ │    88 │
└──────┴───────┘

SELECT A.name, B.score FROM A LEFT JOIN B ON A.id = B.id

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │     0 │
└─────────┴───────┘

A テーブルの Charlie の行と、B テーブルのスコア 88 の行は、JOIN キーが NULL のため、結果に含まれていないことに注意してください。 NULL 値同士を一致させたい場合は、isNotDistinctFrom 関数を使用して JOIN キーを比較してください。

SELECT A.name, B.score FROM A LEFT JOIN B ON isNotDistinctFrom(A.id, B.id)

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │    88 │
└─────────┴───────┘

浮動小数点の JOIN キーに含まれる NaN 値には、上記の NULL ルールは適用されません。 2 つの NaN 値をスカラーとして比較した場合 (NaN = NaN) 、結果は 0 ですが、JOIN キーはスカラーの意味論では比較されないため、NaN キーが一致する可能性があります。実際に一致するかどうかは実装の詳細であり、JOIN アルゴリズム、キーの型、セッション設定によって異なります。特定の動作に依存しないでください。 NaN の行を一致させない必要がある場合は、NULL 値にマッピングしてください: ON if(isNaN(A.id), NULL, A.id) = B.id。 ASOF JOIN では、最も近い一致を判定するカラムは順序に基づいて比較されますが、NaN は順序付けをサポートしないため、そのような行を両側で除外してください。

ASOF JOIN の使い方

ASOF JOIN は、完全に一致するレコードがない場合に、それらを結合する際に役立ちます。この JOIN アルゴリズムでは、テーブル内に特別なカラムが必要です。このカラムは次の条件を満たしている必要があります。

順序付けされた数列を含んでいる必要があります。
次のいずれかの型である必要があります: Int, UInt, Float, Date, DateTime, Decimal。
hash JOIN アルゴリズムでは、JOIN 句内で唯一のカラムにすることはできません。

構文 ASOF JOIN ... ON:

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

等価条件は任意の数だけ使用でき、最も近い一致条件はちょうど 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:

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

ASOF JOIN は、等価条件での結合に equi_columnX を使用し、table_1.asof_column >= table_2.asof_column 条件のもとで最も近い一致による結合に 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 のイベントを見つけることができます。利用可能であれば、同じタイムスタンプ値が最も近いものとして扱われます。ここでは、等値で 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 の結果は、左側のサブクエリのすべてのカラムに、右側のサブクエリのすべてのカラムを続けて含むテーブルです。行は元のテーブル内での位置に基づいて対応付けられます (行の順序が定義されている必要があります) 。サブクエリが返す行数が異なる場合、余分な行は切り捨てられます。例:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers(2)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(2)
    ORDER BY a DESC
) AS t2

┌─a─┬─t2.a─┐
│ 0 │    1 │
│ 1 │    0 │
└───┴──────┘

注: この場合、読み取りが並列に行われると、結果は非決定論的になる可能性があります。例:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers_mt(5)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(10)
    ORDER BY a DESC
) AS t2
SETTINGS max_block_size = 2;

┌─a─┬─t2.a─┐
│ 2 │    9 │
│ 3 │    8 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 0 │    7 │
│ 1 │    6 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 4 │    5 │
└───┴──────┘

分散 JOIN

分散テーブルを含む JOIN を実行する方法は 2 つあります。

通常の JOIN を使用する場合、クエリはリモートサーバーに送信されます。右側のテーブルを作成するために、各サーバー上でサブクエリが実行され、そのテーブルと JOIN が行われます。つまり、右側のテーブルは各サーバーごとに個別に作成されます。
GLOBAL ... JOIN を使用する場合、まずリクエスト元のサーバーがサブクエリを実行して JOIN の片側を計算し、その結果を一時テーブルに格納します。次に、この一時テーブルが各リモートサーバーに渡され、転送された一時データを使ってそれらのサーバー上でクエリが実行されます。LEFT および INNER JOIN では、右側のテーブルがサブクエリとして計算されます。RIGHT JOIN では、保持されるのが右側のテーブルであり、分片から読み取る必要があるため、代わりに左側のテーブルが計算されます。

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

暗黙的な型変換

INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN のクエリでは、「結合キー」に対する暗黙的な型変換がサポートされています。ただし、左側と右側のテーブルの結合キーを単一の型に変換できない場合、クエリは実行できません (たとえば、UInt64 と Int64 の両方の値、あるいは String と Int32 の両方の値を保持できるデータ型は存在しません) 。例テーブル t_1 を考えます:

┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐
│ 1 │ 1 │ UInt16        │ UInt8         │
│ 2 │ 2 │ UInt16        │ UInt8         │
└───┴───┴───────────────┴───────────────┘

および、テーブル t_2:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │    1 │ Int16         │ Nullable(Int64) │
│  1 │   -1 │ Int16         │ Nullable(Int64) │
│  1 │    1 │ Int16         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

クエリ

SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b);

次の集合を返します:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│  1 │    1 │ Int32         │ Nullable(Int64) │
│  2 │    2 │ Int32         │ Nullable(Int64) │
│ -1 │    1 │ Int32         │ Nullable(Int64) │
│  1 │   -1 │ Int32         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

利用上の推奨事項

空またはNULLのセルの処理

テーブルを結合すると、空のセルが生じることがあります。設定 join_use_nulls では、ClickHouse がこれらのセルをどのように補完するかを定義します。 JOIN のキーが Nullable のフィールドである場合、少なくとも1つのキーの値が NULL である行は結合されません。

構文

USING で指定するカラムは、両方のサブクエリで同じ名前である必要があり、それ以外のカラムは異なる名前である必要があります。サブクエリ内のカラム名を変更するには、別名を使用できます。 USING 句では、結合に使用する1つ以上のカラムを指定します。これにより、それらのカラムが等しいものとして扱われます。カラムのリストは括弧を付けずに指定します。より複雑な結合条件はサポートされていません。

構文上の制限事項

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 セクションを参照してください。

メモリ制限

デフォルトでは、ClickHouse はハッシュ結合アルゴリズムを使用します。ClickHouse は right_table を受け取り、そのためのハッシュテーブルを RAM 上に作成します。join_algorithm = 'auto' が有効な場合、メモリ使用量があるしきい値を超えると、ClickHouse は merge 結合アルゴリズムにフォールバックします。JOIN アルゴリズムの説明については、join_algorithm 設定を参照してください。 JOIN 操作のメモリ使用量を制限する必要がある場合は、次の設定を使用します。

max_rows_in_join — ハッシュテーブル内の行数を制限します。
max_bytes_in_join — ハッシュテーブルのサイズを制限します。

これらの制限のいずれかに達すると、ClickHouse は join_overflow_mode 設定で指定されたとおりに動作します。この 2 つはハードな上限であり、結合をディスクにスピルさせることは決してありません。そのため、これらをスピルのしきい値と同じかそれ以下に設定すると、通常はスピルが発生する前にクエリが停止します。ただし、次の 2 つの設定はこの挙動を変更します。enable_adaptive_memory_spill_scheduler は、以下のしきい値によってスピル可能となった結合を引き続きスピルさせることができ、 legacy_join_size_limits_trigger_spilling は、この 2 つの上限を再びディスクへのスピルのトリガーとして扱います。結合を失敗させる代わりに、右側をディスクにスピルして実行を継続させるには、次を使用します。

max_bytes_before_external_join — スピルの絶対しきい値。
max_bytes_ratio_before_external_join — 利用可能メモリに対する比率で指定するスピルのしきい値。

これらは、grace_hash を含むすべてのハッシュベースのアルゴリズムに対する、しきい値に基づくスピルのトリガーです。メモリ逼迫時には、enable_adaptive_memory_spill_scheduler によって指定されたしきい値より早くスピルすることがありますが、それはいずれかの値が 0 以外である場合に限られます。しきい値がまったく設定されていない結合は決してスピルしないためです。選択した join_algorithm は、結合がどのようにスピルするか (grace_hash は最初の block から右テーブルをパーティション分割し、hash と parallel_hash はメモリ上に収集してしきい値を超えた時点で切り替える) を決めるものであり、これらの設定が適用されるかどうかを決めるものではありません。唯一の例外は legacy_join_size_limits_trigger_spilling で、これを有効にすると、単独で使用される grace_hash は両方のしきい値を無視し、代わりに 2 つのハードな上限に基づいてスピルします。

例

例:

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘

SQLリファレンス

データ型

エンジン

関数

フォーマット

設定

システムテーブル

データレイク

サポートされる JOIN の種類

LATERAL JOIN

設定

ON セクションの条件

異なるテーブルのカラムに対する不等式条件付き JOIN

任意の ON 条件による JOIN

JOIN の結合キーにおける NULL 値と NaN 値

ASOF JOIN の使い方

PASTE JOIN の使用法

分散 JOIN

暗黙的な型変換

利用上の推奨事項

空またはNULLのセルの処理

構文

構文上の制限事項

パフォーマンス

メモリ制限

例

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

​LATERAL JOIN

​設定

​ON セクションの条件

​異なるテーブルのカラムに対する不等式条件付き JOIN

​任意の ON 条件による JOIN

​JOIN の結合キーにおける NULL 値と NaN 値

​ASOF JOIN の使い方

​PASTE JOIN の使用法

​分散 JOIN

​暗黙的な型変換

​利用上の推奨事項

​空またはNULLのセルの処理

​構文

​構文上の制限事項

​パフォーマンス

​メモリ制限

​例

​関連コンテンツ

サポートされる JOIN の種類

LATERAL JOIN

設定

ON セクションの条件

異なるテーブルのカラムに対する不等式条件付き JOIN

任意の ON 条件による JOIN

JOIN の結合キーにおける NULL 値と NaN 値

ASOF JOIN の使い方

PASTE JOIN の使用法

分散 JOIN

暗黙的な型変換

利用上の推奨事項

空またはNULLのセルの処理

構文

構文上の制限事項

パフォーマンス

メモリ制限

例

関連コンテンツ