跳到主要内容
跳到主要内容
编辑此页

JOIN 子句

Join 通过使用每个表中通用的值，将来自一个或多个表的列组合来生成新表。这是支持 SQL 的数据库中的常见操作，对应于 关系代数 连接。单表连接的特殊情况通常称为“自连接”。

语法

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 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）禁用标准 JOIN 类型的笛卡尔积。
  • ASOF JOIN 和 LEFT ASOF JOIN，连接具有非精确匹配的序列。ASOF JOIN 的用法如下所述。
  • PASTE JOIN，执行两个表的水平连接。
注意

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

带有单连接键条件和 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 │
└───┴────┴─────┘

连接来自不同表的列的不等式条件​

除了相等条件外，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 │
└─────────┴───────┘

请注意，由于 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 非常有用。

算法需要在表中包含特殊列。此列

  • 必须包含有序序列。
  • 可以是以下类型之一：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

您可以使用任意数量的相等条件和一个最接近匹配条件。例如，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 时，查询会发送到远程服务器。子查询在每个服务器上运行，以便创建右表，并使用此表执行连接。换句话说，右表是在每个服务器上单独形成的。
  • 当使用 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 字段，则至少有一个键的值为 NULL 的行不会被连接。

语法​

USING 中指定的列在两个子查询中必须具有相同的名称，而其他列的名称必须不同。您可以使用别名来更改子查询中列的名称。

USING 子句指定要连接的一个或多个列，从而建立这些列的相等性。列列表设置为不带括号。不支持更复杂的连接条件。

语法限制​

对于单个 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 会回退到 合并 连接算法。有关 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 │
└───────────┴────────┴────────┘