JOIN 子句 - ClickHouse Documentation

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

支持的 JOIN 类型

支持所有标准的 SQL JOIN 类型：

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 还支持以下额外的 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`	对两个表执行横向拼接。

当 join_algorithm 设置为 partial_merge 时，RIGHT JOIN 和 FULL JOIN 仅支持 ALL 严格性 (不支持 SEMI、ANTI、ANY 和 ASOF) 。

设置

默认的 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 中每个包含连接键的 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 │
└────┴──────┘     └────┴────────────────┴────────┘

带有一个连接键条件以及 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 类型的 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 │
└──────┴────────┴────────┘

使用 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 类型 JOIN 且条件包含 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 这类条件的 Experimental 支持，更多详情请参阅下文。

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 中，除等值条件外，还可使用不等条件。不等条件仅支持 hash 和 grace_hash JOIN 算法。启用 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 │
└─────────┴───────┘

请注意，由于 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 算法，它不能是 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 使用 hash join 算法。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 设置中的指示执行。

示例

示例：

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

​支持的 JOIN 类型

​设置

​ON 部分中的条件

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

​JOIN 连接键中的 NULL 值

​ASOF JOIN 用法

​PASTE JOIN 用法

​Distributed JOIN

​隐式类型转换

​使用建议

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

​语法

​语法限制

​性能

​内存限制

​示例

​相关内容

支持的 JOIN 类型

设置

`ON` 部分中的条件

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

JOIN 连接键中的 NULL 值

ASOF JOIN 用法

PASTE JOIN 用法

Distributed JOIN

隐式类型转换

使用建议

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

语法

语法限制

性能

内存限制

示例

相关内容