跳到主要内容
跳到主要内容

JOIN子句

JOIN子句通过使用各表中共同的值结合一个或多个表的列来生成新表。这是一种在支持SQL的数据库中常见的操作，对应于关系代数的连接。单表连接的特例通常称为“自连接”。

语法

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）禁用标准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设置覆盖默认的连接类型。

ClickHouse服务器对于ANY JOIN操作的行为取决于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：

包含一个连接键条件和一个对table_2的附加条件的查询：

请注意，结果中包含名字为C且文本列为空的行。由于使用了OUTER类型的连接，因此它被包含在结果中。

使用INNER类型连接和多个条件的查询：

结果：

使用INNER类型连接和带有OR条件的查询：

结果：

使用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，请查看下面的部分以获取更多详细信息。

结果：

带有不同表的列的不等式条件的JOIN​

Clickhouse当前支持ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN，并使用不等式条件以补充等式条件。不等式条件仅支持hash和grace_hash连接算法。使用join_use_nulls时不支持不等式条件。

示例

表t1：

表t2

JOIN键中的NULL值​

NULL不等于任何值，包括它自己。这意味着如果一个JOIN键在一个表中有NULL值，它将与另一个表中的NULL值不匹配。

示例

表A：

表B：

请注意，表A中的Charlie行和表B中的得分为88的行未出现在结果中，因为JOIN键中有NULL值。

如果您想匹配NULL值，可以使用isNotDistinctFrom函数比较JOIN键。

ASOF JOIN用法​

ASOF JOIN在您需要连接没有精确匹配的记录时非常有用。

此JOIN算法在表中需要一个特殊列。该列：

  • 必须包含一个有序序列。
  • 可以是以下类型之一：Int, UInt、Float、Date、DateTime、Decimal。
  • 对于hash连接算法，它不能是JOIN子句中唯一的列。

语法ASOF JOIN ... ON：

您可以使用任意数量的等式条件和恰好一个最近匹配条件。例如，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：

ASOF JOIN使用equi_columnX进行等式连接，使用asof_column进行最近匹配连接，条件为table_1.asof_column >= table_2.asof_column。asof_column列始终是在USING子句中的最后一个列。

例如，考虑下面的表：

ASOF JOIN可以从table_1获取用户事件的时间戳，并找到table_2中时间戳最接近于来自table_1的事件时间戳的事件，满足最近匹配条件。如果可用，相等的时间戳值就是最近的。在我们的例子中，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的结果是一个包含左侧子查询所有列的表，随后是右侧子查询的所有列。 根据原始表中的位置匹配行（行的顺序应该被定义）。 如果子查询返回不同数量的行，则多余的行会被截断。

示例：

注意：在这种情况下，如果读取是并行的，结果可能是不确定的。例如：

分布式JOIN​

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

  • 当使用普通的JOIN时，查询被发送到远程服务器。在每个服务器上运行子查询以生成右表，连接在此表上执行。换句话说，右表在每个服务器上单独生成。
  • 当使用GLOBAL ... JOIN时，请求服务器首先运行一个子查询来计算右表。此临时表被传递到每个远程服务器，在它们上使用传输的临时数据运行查询。

使用GLOBAL时要小心。有关更多信息，请参见分布式子查询部分。

隐式类型转换​

INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN查询支持“连接键”的隐式类型转换。但是，如果左表和右表中的连接键无法转换为单一类型，则无法执行查询（例如，不能有数据类型可以同时容纳所有来自UInt64和Int64的值，或 String和Int32）。

示例

考虑表t_1：

以及表t_2：

查询

返回集合：

使用建议​

处理空或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取右表并在RAM中为其创建哈希表。如果启用join_algorithm = 'auto'，那么在某些内存消耗阈值后，ClickHouse将回退到归并连接算法。有关JOIN算法的描述，请参见join_algorithm设置。

如果您需要限制JOIN操作的内存消耗，请使用以下设置：

达到这些限制中的任何一个时，ClickHouse将按照join_overflow_mode设置指示的方式进行处理。

示例​

示例：

相关内容​