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

JOIN句

JOIN句は、共通の値を使用して、1つまたは複数のテーブルからのカラムを結合することによって新しいテーブルを生成します。これは、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句からのカラムは「結合キー」と呼ばれます。特に記載されていない限り、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パーシャル（LEFTとRIGHTの対向側用）または完全（INNERとFULL用）にデカルト積を無効にします。
ASOF JOIN, LEFT ASOF JOIN非正確な一致でシーケンスを結合します。ASOF JOINの使用法は以下で説明します。
PASTE JOIN2つのテーブルの水平連結を行います。
注記

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設定に依存します。

参照：

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を考えます：

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

結果には、名前が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を含む条件のクエリ：

注記

デフォルトでは、同じテーブルからのカラムを使用する限り、不等号条件がサポートされます。 たとえば、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 │
└───┴────┴─────┘

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

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はどの値とも等しくなく、自己を含みます。これは、1つのテーブルの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 │
└─────────┴───────┘

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

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は、正確な一致がないレコードを結合する必要がある場合に便利です。

この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を最も近い一致での結合に使用します。この条件は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_2でtable_1のイベントのタイムスタンプに最も近いイベントを見つけることができます。等しいタイムスタンプ値は、利用可能な場合は最も近いものです。ここで、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の結果は、左のサブクエリからのすべてのカラムの後に右のサブクエリからのすべてのカラムが続くテーブルです。 行は、元のテーブルにおける位置に基づいて一致します（行の順序は定義されるべきです）。 サブクエリが異なる数の行を返す場合、余分な行はカットされます。

例：

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を使用する場合、クエリはリモートサーバーに送信されます。サブクエリはそれぞれのサーバー上で実行され、正しいテーブルを作成し、そのテーブルで結合が実行されます。言い換えれば、正しいテーブルはそれぞれのサーバーで別々に形成されます。
  • 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つ以上のカラムを指定します。カラムのリストは括弧なしで設定されます。より複雑な結合条件はサポートされません。

構文制限​

単一のSELECTクエリ内の複数のJOIN句に対して：

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

ON、WHERE、およびGROUP BY句に関して：

  • ON、WHERE、およびGROUP BY句に任意の式を使用することはできませんが、SELECT句で式を定義し、その後これらの句でエイリアスを介して使用することが可能です。

パフォーマンス​

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

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

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

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

メモリ制限​

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

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

関連コンテンツ​