JOIN句

JOINは、共通の値を使用して複数のテーブルのカラムを組み合わせることで、新しいテーブルを生成します。これは、SQLをサポートするデータベースで一般的な操作であり、関係代数の結合に対応します。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句からのカラムは「結合キー」と呼ばれます。特に指定がない限り、結合は一致する「結合キー」を持つ行からの直積を生成し、元のテーブルよりもはるかに多くの行を持つ結果を生成する可能性があります。

関連コンテンツ

• ブログ: 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

サポートされる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つのテーブルを水平方向に結合します。

Note

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_optimizations
• partial_merge_join_rows_in_right_blocks
• join_on_disk_max_files_to_merge
• any_join_distinct_right_table_keys

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

ONセクションの条件

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

条件が満たされると行が結合されます。条件が満たされない場合でも、JOINタイプによっては行が結果に含まれることがあります。注意すべき点は、同じ条件がWHEREセクションに配置され、条件が満たされていない場合、行は常に結果からフィルタリングされます。

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

Note

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

例

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に対する追加の条件を持つクエリ：

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

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

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

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

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');

結果：

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

INNERタイプの結合とORを含む条件を持つクエリ：

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;

結果：

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

INNERタイプの結合とORおよびANDを含む条件を持つクエリ：

Note

デフォルトでは、異なるテーブルからのカラムを使う条件はサポートされません。例えば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のような条件のエクスペリメンタルサポートを試みることができます。詳細については以下を参照してください。

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

結果：

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

[試験的機能] 異なるテーブルのカラムに対する不等式条件を伴う結合

Note

この機能は試験的です。これを利用するには、設定ファイルやSETコマンドを用いてallow_experimental_join_conditionを1に設定してください：

SET allow_experimental_join_condition=1

そうでなければ、INVALID_JOIN_ON_EXPRESSIONが返されます。

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

例

テーブル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

JOINキー内のNULL値

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 │
└─────────┴───────┘

ASOF JOINの使用方法

ASOF JOINは、正確な一致がないレコードを結合するときに役立ちます。

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

• 順序付けられたシーケンスを含まなければならない
• 次のいずれかの型を持つことができる: Int, UInt, Float, Date, DateTime, Decimal
• hash結合アルゴリズムでは、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を等価結合に使用し、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は結合されません。

Note

ASOF JOINはhashおよびfull_sorting_merge結合アルゴリズムのみでサポートされます。 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を使用する場合、まずリクエスタサーバーが右テーブルを計算するためのサブクエリを実行します。この一時テーブルは各リモートサーバーに渡され、送信された一時データを使用してクエリが実行されます。

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を伴うクエリを実行するたびにサブクエリが再度実行されます。結果はキャッシュされません。これを避けるために、特殊なJoinテーブルエンジンを使用します。このエンジンは、常にRAMにある結合のための準備された配列です。

場合によっては、JOINの代わりにINを使用する方が効率的です。

ディメンションテーブル（広告キャンペーンの名前などのディメンションプロパティを含む比較的小さなテーブル）との結合にJOINが必要な場合、右テーブルが毎回再アクセスされるためJOINはあまり便利ではないかもしれません。そういった場合には「Dictionary」機能を使用することをお勧めします。詳細はDictionariesセクションを参照してください。

メモリ制限

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

JOIN操作のメモリ消費を制限する必要がある場合、以下の設定を使用します：

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

これらの制限のいずれかが到達された場合、ClickHouseはjoin_overflow_mode設定に指示された通りに動作します。

例

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 │
└───────────┴────────┴────────┘