JOIN

JOIN 子句通过基于各表中的公共值组合一个或多个表的列，生成一张新表。这是在支持 SQL 的数据库中常见的操作，对应于关系代数中的 join。一张表与自身进行 join 的特殊情况通常称为“自连接”。语法

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 会对连接键匹配的行生成笛卡儿积，因此结果中的行数可能远多于源表。

支持的连接类型

支持所有标准的 SQL 连接类型：

Type	Description
`INNER JOIN`	仅返回匹配的行。
`LEFT OUTER JOIN`	除返回匹配的行外，还返回左表中不匹配的行。
`RIGHT OUTER JOIN`	除返回匹配的行外，还返回右表中不匹配的行。
`FULL OUTER JOIN`	除返回匹配的行外，还返回两个表中不匹配的行。
`CROSS JOIN`	生成整个表的笛卡儿积，不指定“连接键”。
`NATURAL JOIN`	自动基于两个表中所有同名列进行连接；每个公共列在结果中只出现一次。支持 `INNER` (默认) 、`LEFT`、`RIGHT` 和 `FULL` 变体。等价于 `JOIN ... USING (col1, col2, ...)`，其中列列表会自动推导。

未指定类型的 JOIN 默认为 INNER。
关键字 OUTER 可以安全省略。
CROSS JOIN 的另一种语法是在 FROM 子句中用逗号分隔指定多个表。
如果 NATURAL JOIN 没有可匹配的列，其行为与 CROSS JOIN 相同。

ClickHouse 还支持以下额外的连接类型：

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`	对两个表执行横向拼接。

使用分析器 (analyzer) 时，如果禁用 semi_join_include_columns_from_both_sides 或 anti_join_include_columns_from_both_sides 设置，则对于在连接结果生成之后才解析的表达式，相应的连接只会公开其保留侧的列。

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 严格性 (不支持 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 连接或逗号连接组合使用。
仅支持默认的 ALL 严格性；不支持 ANY、SEMI、ANTI 和 ASOF。
除 ON true (也接受 ON 1) 外，不支持其他连接谓词；不支持 USING，且谓词不能省略。请将关联两侧的过滤条件写在 lateral 子查询的 WHERE 子句中。
不支持 GLOBAL 和 LOCAL 连接修饰符。
lateral 子查询必须至少引用左侧的一列。对于非关联子查询，请使用普通连接。
lateral 子查询会针对其所引用左侧列的每个不同取值求值一次，而不是针对左侧的每一行求值一次，因此其中不得包含在单个查询内具有非确定性的函数 (例如 rand 或 generateUUIDv4) ，也不得包含生成随机行的表函数 (例如 generateRandom) 。在单个查询内取值不变的函数 (例如 now) 则可以使用。
仅支持将子查询用作 lateral 表表达式。不支持 PostgreSQL 的表源写法 LATERAL unnest(...) 和 CROSS JOIN UNNEST(...)，请改用 ARRAY JOIN 子句。
lateral 子查询中的 GROUP BY 和 ORDER BY 会针对所有求值结果统一执行一次，因此 max_rows_to_group_by、max_rows_to_sort 和 max_bytes_to_sort 限制统计的是所有求值的总行数，而非单次求值的行数。这些限制仅支持 throw 溢出模式，使用 any 和 break 会被拒绝。
所有求值结果的行通过一次连接与左侧行进行匹配。与任何哈希连接一样， max_rows_in_join 和 max_bytes_in_join 限制的是该连接中驻留在内存中的一侧，具体是哪一侧由查询规划器决定：在默认设置 (correlated_subqueries_use_in_memory_buffer = 1) 下，是 JOIN LATERAL 的左侧，因为必须先完整读取左侧行，才能对 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 设置进行覆盖。 ClickHouse server 对 ANY JOIN 操作的处理方式取决于 any_join_distinct_right_table_keys 设置。 另请参见

使用 cross_to_inner_join_rewrite 设置可定义当 ClickHouse 无法将 CROSS JOIN 重写为 INNER JOIN 时的行为。默认值为 1，表示允许 join 继续执行，但速度会更慢。如果希望抛出错误，请将 cross_to_inner_join_rewrite 设为 0；将其设为 2 时，则不会运行 cross joins，而是强制重写所有逗号/cross joins。如果在该值为 2 时重写失败，你将收到一条错误消息：“Please, try to simplify WHERE section”。

`ON` 部分中的条件

ON 部分可以包含多个通过 AND 和 OR 运算符组合起来的条件。用于指定连接键的条件必须：

同时引用左表和右表
使用等值运算符

其他条件可以使用其他运算符，并且可以引用查询中的左表或右表，也可以同时引用两者。只有整个复合条件都满足时，行才会被连接。如果条件不满足，根据 JOIN 类型，这些行仍然可能出现在结果中。请注意，如果将相同的条件放在 WHERE 部分中且条件不满足，那么这些行始终会被从结果中过滤掉。 ON 子句中的 OR 运算符通过哈希 JOIN 算法起作用——对于 JOIN 中每个包含连接键的 OR 参数，都会创建一张单独的哈希表，因此，随着 ON 子句中 OR 表达式数量增加，内存消耗和查询执行时间也会线性增长。

只有等值运算符 (=) 才能使条件成为连接键。不同表的列之间也可以使用其他运算符，但如果没有任何等值条件，连接就必须逐一检查每一对行，速度会慢得多；参见使用任意 ON 条件的连接。

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

带有一个连接键条件以及 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 且文本列为空的那一行。之所以会出现在结果中，是因为使用了 OUTER 类型的连接。

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

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

使用 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) 同样受支持，更多详情请参阅下文各节。

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

对不同表中的列使用带不等条件的连接

ClickHouse 支持在 ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN 中，除相等条件外，还可使用不等条件。当 ON 子句中除不等条件外还包含两个表之间的相等条件时，该相等条件将作为连接键，不等条件则在其匹配到的行上进行检查。此类混合条件仅支持 hash、parallel_hash 和 grace_hash 连接算法。当 ON 子句中不包含两个表之间的相等条件时，就没有可供匹配的连接键。此时，如果条件是一对不等式，该连接将由 ie_join 执行；否则将以 block nested loop 连接方式执行。连接过程中求值的条件不能包含 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 子句可以是基于两个表中列的任意布尔表达式，例如范围检查、算术比较或标量函数调用。如果其中不包含两个表之间的相等条件，就没有可用于匹配行的连接键。如果 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 通常会为共享同一连接键的每组行保留一行。此处不存在这样的分组，因此它们会为驱动连接的那张表的每一行保留一行：LEFT ANY 和 LEFT SEMI 对每个左表行最多输出一次，RIGHT ANY 和 RIGHT SEMI 对每个右表行最多输出一次。ANY INNER 会同时限制两侧——任一张表的每一行最多只使用一次，因此结果行数不会超过较小那张表的行数。结果具体由哪些行对构成是任意的，这正是 ANY 的语义所在，同一查询的两次运行结果也可能不同。对于 ANY INNER，这种不确定性还会波及行数：按照行被检查的任意顺序，以贪心方式为两侧的每一行配对，最终未配对的行数可能各不相同。因此，对没有连接键的 ANY INNER 连接执行 count() 的结果不可复现，具体取决于线程数量以及右表的物理顺序。性能由于每一对行都要检查，工作量会随两张表行数的乘积而非行数之和增长。因此，在相同数据上，block nested loop join 的开销比哈希连接高出几个数量级，而且表越大，差距越明显。如果查询能够在 ON 子句中包含至少一个等值条件，请尽量采用这种写法。 LEFT ANY、ANY INNER、LEFT SEMI 和 LEFT ANTI 连接在找到某个左表行的第一条匹配行后，就会停止扫描右表，因此开销通常低于对应的 ALL 连接。而由右表驱动的对应形式 (RIGHT ANY、RIGHT SEMI、RIGHT ANTI) 则会检查每一对行，因为结果取决于哪些右表行得到了匹配。join_any_take_last_row 在此处不起作用：没有连接键，也就不存在可供选取最后一行的匹配行分组。内存占用以已物化的右表为上限，不会随结果集的增大而增长。右表与其他 JOIN 算法一样受相同设置的约束，这些设置列于内存限制中：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 │
└─────────┴───────┘

请注意，由于 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 │
└─────────┴───────┘

浮点 JOIN 键中的 NaN 值不遵循上述 NULL 规则。两个 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

你可以使用任意数量的相等条件，以及恰好一个最近匹配条件。例如，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 算法支持。 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 │
└───┴──────┘

Distributed JOIN

执行涉及分布式表的 JOIN 有两种方式：

使用普通 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 字段，则键中只要有一个值为 NULL，对应的行就不会被连接。

语法

USING 中指定的列在两个子查询中必须同名，其他列则必须使用不同的名称。你可以使用别名来修改子查询中列的名称。 USING 子句用于指定一个或多个参与 join 的列，从而要求这些列相等。列列表不带括号。更复杂的 join 条件不受支持。

语法限制

对于单个 SELECT 查询中的多个 JOIN 子句：

只有在连接的是表而非子查询时，才支持通过 * 选取所有列。
不支持 PREWHERE 子句。
不支持 USING 子句。

对于 ON、WHERE 和 GROUP BY 子句：

ON、WHERE 和 GROUP BY 子句中不能使用任意表达式，但你可以先在 SELECT 子句中定义一个表达式，再通过别名在这些子句中使用它。

性能

运行 JOIN 时，与查询其他阶段相关的执行顺序不会被优化。连接操作 (即在右侧表中查找) 会在 WHERE 过滤和聚合之前执行。每次运行包含相同 JOIN 的查询时，子查询都会重新执行，因为结果不会被缓存。为避免这种情况，请使用专用的 Join 表引擎，它是一种预先构建、用于连接的数据数组，并且始终驻留在 RAM 中。在某些情况下，使用 IN 比使用 JOIN 更高效。如果你需要使用 JOIN 与维度表进行连接 (这类表通常较小，包含维度属性，例如广告活动名称) ，那么 JOIN 可能并不方便，因为每次查询都需要重新访问右侧表。对于这种情况，建议使用“字典”功能来代替 JOIN。更多信息，请参见字典部分。

内存限制

默认情况下，ClickHouse 使用哈希连接算法。ClickHouse 会获取 right_table，并在 RAM 中为其创建哈希表。如果启用了 join_algorithm = 'auto'，那么当内存消耗达到某个阈值后，ClickHouse 会回退到 merge join 算法。有关 JOIN 算法的说明，请参见 join_algorithm 设置。如果需要限制 JOIN 操作的内存消耗，请使用以下设置：

max_rows_in_join — 限制哈希表中的行数。
max_bytes_in_join — 限制哈希表的大小。

当达到这些限制中的任意一个时，ClickHouse 会按照 join_overflow_mode 设置中的指示执行。这两个设置是硬性上限，绝不会让 join 落盘，因此将它们设置为等于或低于落盘阈值时，通常会在查询落盘之前就将其中止。有两个设置会改变这一行为： enable_adaptive_memory_spill_scheduler 仍可让一个因下述阈值而具备落盘能力的 join 落盘；而 legacy_join_size_limits_trigger_spilling 会将这两个硬性上限重新变为磁盘落盘的触发条件。若希望通过将右侧数据落盘而非直接失败来让 join 继续运行，请使用：

max_bytes_before_external_join — 绝对落盘阈值。
max_bytes_ratio_before_external_join — 以可用内存比例表示的落盘阈值。

它们是所有基于哈希的算法 (包括 grace_hash) 基于阈值的落盘触发条件；在内存压力下，enable_adaptive_memory_spill_scheduler 可能会比它们所要求的更早落盘——但前提是其中之一为非零值，因为完全没有阈值的 join 永远不会落盘。你所选择的 join_algorithm 决定的是 join 如何落盘——grace_hash 从第一个块开始就对右表进行分区，hash 和 parallel_hash 则先在内存中收集数据，并在越过阈值时切换——而不是决定这些设置是否生效。唯一的例外是 legacy_join_size_limits_trigger_spilling：启用后，独立使用的 grace_hash 会忽略这两个阈值，转而依据这两个硬性上限落盘。

示例

示例：

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 参考文档

数据类型

引擎

函数

格式

设置

系统表

数据湖

支持的连接类型

LATERAL JOIN

设置

`ON` 部分中的条件

对不同表中的列使用带不等条件的连接

使用任意 ON 条件的 JOIN

JOIN 连接键中的 NULL 和 NaN 值

ASOF JOIN 用法

PASTE JOIN 用法

Distributed JOIN

隐式类型转换

使用建议

空单元格或 NULL 单元格的处理

语法

语法限制

性能

内存限制

示例

​支持的连接类型

​LATERAL JOIN

​设置

​ON 部分中的条件

​对不同表中的列使用带不等条件的连接

​使用任意 ON 条件的 JOIN

​JOIN 连接键中的 NULL 和 NaN 值

​ASOF JOIN 用法

​PASTE JOIN 用法

​Distributed JOIN

​隐式类型转换

​使用建议

​空单元格或 NULL 单元格的处理

​语法

​语法限制

​性能

​内存限制

​示例

​相关内容

支持的连接类型

LATERAL JOIN

设置

`ON` 部分中的条件

对不同表中的列使用带不等条件的连接

使用任意 ON 条件的 JOIN

JOIN 连接键中的 NULL 和 NaN 值

ASOF JOIN 用法

PASTE JOIN 用法

Distributed JOIN

隐式类型转换

使用建议

空单元格或 NULL 单元格的处理

语法

语法限制

性能

内存限制

示例

相关内容