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 会为匹配的“连接键”的行生成 笛卡尔积，这可能会产生比源表更多行数的结果。

支持的 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 时，仅在 ALL 严格性下支持 RIGHT JOIN 和 FULL JOIN（不支持 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_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 子句中 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 │
└───┴────┴─────┘

不同表的列的 JOIN 与不等条件

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 在需要连接没有精确匹配的记录时很有用。

此 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 有两种方法

• 使用正常的 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。有关更多信息，请参阅 Dictionaries 部分。

内存限制

默认情况下，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 │
└───────────┴────────┴────────┘

相关内容

• 博客：ClickHouse：一款具有完整 SQL Join 支持的闪电般快速的 DBMS - 第一部分
• 博客：ClickHouse：一款具有完整 SQL Join 支持的闪电般快速的 DBMS - 幕后花絮 - 第二部分
• 博客：ClickHouse：一款具有完整 SQL Join 支持的闪电般快速的 DBMS - 幕后花絮 - 第三部分
• 博客：ClickHouse：一款具有完整 SQL Join 支持的闪电般快速的 DBMS - 幕后花絮 - 第四部分