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 子句的列被称为“连接键”。 除非另有说明，否则连接将从具有匹配“连接键”的行生成 笛卡尔积，这可能会生成比源表多得多的行。

相关内容

• 博客：ClickHouse：具有完整 SQL 连接支持的超快 DBMS - 第 1 部分
• 博客：ClickHouse：具有完整 SQL 连接支持的超快 DBMS - 幕后 - 第 2 部分
• 博客：ClickHouse：具有完整 SQL 连接支持的超快 DBMS - 幕后 - 第 3 部分
• 博客：ClickHouse：具有完整 SQL 连接支持的超快 DBMS - 幕后 - 第 4 部分

支持的 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 设置。

另请参阅

• join_algorithm
• join_any_take_last_row
• join_use_nulls
• partial_merge_join_optimizations
• partial_merge_join_rows_in_right_blocks
• join_on_disk_max_files_to_merge
• 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 子句的表达式数量的增加而线性增长。

注意

如果条件引用来自不同表的列，那么目前只支持相等运算符（=）。

示例

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

[实验性]使用来自不同表的列的非等条件连接

注意

此功能为实验性功能。 要使用它，请在您的配置文件中或使用SET 命令将 allow_experimental_join_condition 设置为 1

SET allow_experimental_join_condition=1

否则，您将收到 INVALID_JOIN_ON_EXPRESSION。

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

连接键中的 NULL 值

NULL 不等于任何值，包括它本身。 这意味着，如果一个表中的连接键具有 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 │
└─────────┴───────┘

请注意，表 A 中带有 Charlie 的行与表 B 中分数为 88 的行由于 JOIN 密钥中的 NULL 值而未出现在结果中。

如果您想匹配 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 密钥是可空 字段，则至少一个密钥具有值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 操作的内存消耗，请使用以下设置

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