ClickHouse：对不同类型Join的支持

ClickHouse 是一个流行的开源实时分析数据库，旨在为需要在大量数据上进行超低延迟分析查询的用例提供最佳性能。为了在分析应用程序中实现最佳性能，通常需要将表组合在一起进行数据非规范化处理。扁平化表通过避免联接来帮助最小化查询延迟，以换取增量 ETL 复杂性，通常可以接受以获得次秒级查询。

然而，对于一些工作负载，如来自传统数据仓库的工作负载，非规范化数据并不总是实用的。有时，用于分析查询的源数据的一部分需要保持规范化。这些规范化表需要较少的存储空间，并提供数据组合的灵活性，但对于某些类型的分析，它们在查询时间需要联接。

幸运的是，与一些误解相反，ClickHouse 完全支持联接！除了支持所有标准 SQL JOIN 类型外，ClickHouse 还提供了其他对分析工作负载和时间序列分析有用的 JOIN 类型。ClickHouse 允许您在六种不同的算法之间选择联接执行方式，或者允许查询规划器自适应选择并在运行时动态更改算法，具体取决于资源可用性和使用情况。

▌ClickHouse支持的Join类型

我们使用来自关系数据集存储库的标准化IMDB数据集的Venn图和示例查询来解释ClickHouse中可用的Join类型。

创建和加载表的说明在这里。该数据集也可在我们的playground中使用，供希望重现查询的用户使用。

我们将使用示例数据集中的四个表：

以上四个表中的数据代表电影。一部电影可以有一个或多个流派。电影中的角色由演员扮演。图表中的箭头表示外键到主键的关系。例如，genres表中一行的movie_id列包含了movies表中一行的id值。

电影和演员之间存在着多对多的关系。通过使用roles表，这种多对多的关系被规范化为两个一对多的关系。roles表中的每一行包含了movies表和actors表中的id字段的值。

▌内连接Inner Join

内连接会返回每个匹配连接键的行对的列值，其中包含左表中的行的列值和右表中的行的列值。如果一行有多个匹配项，则返回所有匹配项（也就是说，具有匹配连接键的行会产生笛卡尔积）。

下面这个查询通过将电影表与类型表连接来查找每部电影的类型：

SELECT    m.name AS name,    g.genre AS genreFROM movies AS mINNER JOIN genres AS g ON m.id = g.movie_idORDER BY    m.year DESC,    m.name ASC,    g.genre ASCLIMIT 10;
┌─name───────────────────────────────────┬─genre─────┐│ Harry Potter and the Half-Blood Prince │ Action    ││ Harry Potter and the Half-Blood Prince │ Adventure ││ Harry Potter and the Half-Blood Prince │ Family    ││ Harry Potter and the Half-Blood Prince │ Fantasy   ││ Harry Potter and the Half-Blood Prince │ Thriller  ││ DragonBall Z                           │ Action    ││ DragonBall Z                           │ Adventure ││ DragonBall Z                           │ Comedy    ││ DragonBall Z                           │ Fantasy   ││ DragonBall Z                           │ Sci-Fi    │└────────────────────────────────────────┴───────────┘
10 rows in set. Elapsed: 0.126 sec. Processed 783.39 thousand rows, 21.50 MB (6.24 million rows/s., 171.26 MB/s.)

请注意，Inner 关键字可以省略。可以使用以下其他连接类型之一来扩展或更改 Inner Join 的行为。

▌(Left/Right/Full) Outer Join

Left Outer Join 与 Inner Join 的行为相同，但对于不匹配的左表行，ClickHouse 会返回右表列的默认值。

Right Outer Join 查询类似，并返回右表中不匹配行的值，以及左表列的默认值。

Full Outer Join 查询将左右外连接结合起来，返回左表和右表中不匹配行的值，并分别使用左表和右表的默认值填充列。

请注意，ClickHouse 可以配置为返回 NULL 而不是默认值（但出于性能原因，这种方法不太推荐）。

此查询通过查询所有在 genres 表中没有匹配项的 movies 表行来查找没有流派的所有电影，因此在查询时间获取 movie_id 列的默认值 0：​​​​​​​

SELECT m.nameFROM movies AS mLEFT JOIN genres AS g ON m.id = g.movie_idWHERE g.movie_id = 0ORDER BY    m.year DESC,    m.name ASCLIMIT 10;

┌─name──────────────────────────────────────┐│ """Pacific War, The"""                    ││ """Turin 2006: XX Olympic Winter Games""" ││ Arthur, the Movie                         ││ Bridge to Terabithia                      ││ Mars in Aries                             ││ Master of Space and Time                  ││ Ninth Life of Louis Drax, The             ││ Paradox                                   ││ Ratatouille                               ││ """American Dad"""                        │└───────────────────────────────────────────┘
10 rows in set. Elapsed: 0.092 sec. Processed 783.39 thousand rows, 15.42 MB (8.49 million rows/s., 167.10 MB/s.)

▌Cross Join

因为Cross Join不考虑任何连接键，它会对两个表进行完全的笛卡尔积运算，即左表的每一行都会与右表的每一行组合。因此，以下查询将电影表中的每一行与类别表中的每一行组合：​​​​​​​

SELECT    m.name,    m.id,    g.movie_id,    g.genreFROM movies AS mCROSS JOIN genres AS gLIMIT 10;
┌─name─┬─id─┬─movie_id─┬─genre───────┐│ #28  │  0 │        1 │ Documentary ││ #28  │  0 │        1 │ Short       ││ #28  │  0 │        2 │ Comedy      ││ #28  │  0 │        2 │ Crime       ││ #28  │  0 │        5 │ Western     ││ #28  │  0 │        6 │ Comedy      ││ #28  │  0 │        6 │ Family      ││ #28  │  0 │        8 │ Animation   ││ #28  │  0 │        8 │ Comedy      ││ #28  │  0 │        8 │ Short       │└──────┴────┴──────────┴─────────────┘
10 rows in set. Elapsed: 0.024 sec. Processed 477.04 thousand rows, 10.22 MB (20.13 million rows/s., 431.36 MB/s.)

前面的例子查询本身意义不大，但可以通过添加where子句来扩展，以关联匹配的行以复制查找每部电影的类型（s）的内部连接行为：​​​​​​​

SELECT    m.name,    g.genreFROM movies AS mCROSS JOIN genres AS gWHERE m.id = g.movie_idORDER BY    m.year DESC,    m.name ASCLIMIT 10;
┌─name───────────────────────────────────┬─genre─────┐│ Harry Potter and the Half-Blood Prince │ Action    ││ Harry Potter and the Half-Blood Prince │ Adventure ││ Harry Potter and the Half-Blood Prince │ Family    ││ Harry Potter and the Half-Blood Prince │ Fantasy   ││ Harry Potter and the Half-Blood Prince │ Thriller  ││ DragonBall Z                           │ Action    ││ DragonBall Z                           │ Sci-Fi    ││ DragonBall Z                           │ Fantasy   ││ DragonBall Z                           │ Comedy    ││ DragonBall Z                           │ Adventure │└────────────────────────────────────────┴───────────┘
10 rows in set. Elapsed: 0.441 sec. Processed 783.39 thousand rows, 21.50 MB (1.78 million rows/s., 48.78 MB/s.)

一种交叉连接的替代语法是在 from 子句中用逗号分隔多个表。

如果查询的 where 部分有连接表达式，ClickHouse 将交叉连接重写为内部连接。

我们可以通过 EXPLAIN SYNTAX（返回查询执行之前重写为的句法优化版本）来检查示例查询的情况：​​​​​​​

EXPLAIN SYNTAXSELECT    m.name AS name,    g.genre AS genreFROM movies AS mCROSS JOIN genres AS gWHERE m.id = g.movie_idORDER BY    m.year DESC,    m.name ASC,    g.genre ASCLIMIT 10;
┌─explain─────────────────────────────────────┐│ SELECT                                      ││     name AS name,                           ││     genre AS genre                          ││ FROM movies AS m                            ││ ALL INNER JOIN genres AS g ON id = movie_id ││ WHERE id = movie_id                         ││ ORDER BY                                    ││     year DESC,                              ││     name ASC,                               ││     genre ASC                               ││ LIMIT 10                                    │└─────────────────────────────────────────────┘
11 rows in set. Elapsed: 0.077 sec.

在经过语法优化后的交叉连接查询版本中，Inner Join子句包含了all关键字。该关键字被显式添加，以保持Cross Join的笛卡尔积语义，即使它被重写为Inner Join，笛卡尔积也可以被禁用。

正如上文提到的，对于Right Outer Join，Outer关键字可以省略，并且可以添加可选的all关键字。您可以写All Right Join，它会正常工作。

▌Left/Right Semi Join

Left/Right Semi Join是一种特殊的Join，它只返回符合Join条件的左/右表的行。Left Semi Join只返回左表中至少存在一个Join条件匹配的行，Right Semi Join类似，只返回右表中至少存在一个Join条件匹配的行。但是只返回第一条匹配的结果，不会产生笛卡尔积。

下面的查询找到了所有在2023年参演过电影的演员/女演员。请注意，如果使用常规的（Inner）Join，则如果演员在2023年有多个角色，则会多次出现：​​​​​​​

SELECT    a.first_name,    a.last_nameFROM actors AS aLEFT SEMI JOIN roles AS r ON a.id = r.actor_idWHERE toYear(created_at) = '2023'ORDER BY id ASCLIMIT 10;
┌─first_name─┬─last_name──────────────┐│ Michael    │ 'babeepower' Viera     ││ Eloy       │ 'Chincheta'            ││ Dieguito   │ 'El Cigala'            ││ Antonio    │ 'El de Chipiona'       ││ José       │ 'El Francés'           ││ Félix      │ 'El Gato'              ││ Marcial    │ 'El Jalisco'           ││ José       │ 'El Morito'            ││ Francisco  │ 'El Niño de la Manola' ││ Víctor     │ 'El Payaso'            │└────────────┴────────────────────────┘
10 rows in set. Elapsed: 0.151 sec. Processed 4.25 million rows, 56.23 MB (28.07 million rows/s., 371.48 MB/s.)

▌Left/Right Anti Join

左反连接返回左表中所有未匹配的行的列值。

类似地，右反连接返回右表中所有未匹配的行的列值。

我们之前示例的外连接查询可以用反连接重写，用于查找数据集中没有分类的电影：

SELECT m.nameFROM movies AS mLEFT ANTI JOIN genres AS g ON m.id = g.movie_idORDER BY    year DESC,    name ASCLIMIT 10;
┌─name──────────────────────────────────────┐│ """Pacific War, The"""                    ││ """Turin 2006: XX Olympic Winter Games""" ││ Arthur, the Movie                         ││ Bridge to Terabithia                      ││ Mars in Aries                             ││ Master of Space and Time                  ││ Ninth Life of Louis Drax, The             ││ Paradox                                   ││ Ratatouille                               ││ """American Dad"""                        │└───────────────────────────────────────────┘
10 rows in set. Elapsed: 0.077 sec. Processed 783.39 thousand rows, 15.42 MB (10.18 million rows/s., 200.47 MB/s.)

▌Left/Right/Inner Any Join

Left Any Join是左外连接+左半连接的组合，这意味着ClickHouse返回每个左表的行的列值，或者与右表的匹配行的列值组合，或者与右表的默认列值组合，如果没有匹配项。如果左表的一行在右表中有多个匹配项，则ClickHouse仅返回第一个匹配项的组合列值（禁用笛卡尔积）。

同样，Right Any Join是Right Outer Join + Right Semi Join的组合。

Inner Any Join是禁用笛卡尔积的Inner Join。

我们使用两个临时表（left_table和right_table）使用values表函数构建一个抽象示例来演示Left Any Join。

WITH    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))SELECT    l.c AS l_c,    r.c AS r_cFROM left_table AS lLEFT ANY JOIN right_table AS r ON l.c = r.c;
┌─l_c─┬─r_c─┐│   1 │   0 ││   2 │   2 ││   3 │   3 │└─────┴─────┘
3 rows in set. Elapsed: 0.002 sec.

这是使用 Right Any Join 的相同查询：

WITH    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))SELECT    l.c AS l_c,    r.c AS r_cFROM left_table AS lRIGHT ANY JOIN right_table AS r ON l.c = r.c;
┌─l_c─┬─r_c─┐│   2 │   2 ││   2 │   2 ││   3 │   3 ││   3 │   3 ││   0 │   4 │└─────┴─────┘
5 rows in set. Elapsed: 0.002 sec.

以下是使用 Inner Any Join 的查询：

WITH    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))SELECT    l.c AS l_c,    r.c AS r_cFROM left_table AS lINNER ANY JOIN right_table AS r ON l.c = r.c;
┌─l_c─┬─r_c─┐│   2 │   2 ││   3 │   3 │└─────┴─────┘
2 rows in set. Elapsed: 0.002 sec.

▌ASOF Join

ASOF Join是由Martijn Bakker和Artem Zuikov在2019年为ClickHouse实现的，它提供了非精确匹配的能力。如果左表中的一行在右表中没有完全匹配的行，则会使用最接近的匹配行作为匹配。

这在时间序列分析中特别有用，可以显著减少查询复杂性。

我们将以股票市场数据的时间序列分析为例。quotes表包含特定时间的股票符号报价。在我们的示例数据中，价格每10秒钟更新一次。trades表列出股票交易-在特定时间买入了特定数量的股票：

为了计算每次交易的具体成本，我们需要将交易与其最接近的报价时间进行匹配。

使用ASOF Join实现这一点很简单，我们可以使用ON子句来指定一个精确的匹配条件，使用AND子句来指定最接近的匹配条件——我们正在寻找在交易日期之前或者正好等于交易日期的最接近的quotes表中的行：

SELECTt.symbol,t.volume,t.time AS trade_time,q.time AS closest_quote_time,q.price AS quote_price,t.volume * q.price AS final_priceFROM trades tASOF LEFT JOIN quotes q ON t.symbol = q.symbol AND t.time >= q.timeFORMAT Vertical;
Row 1:──────symbol:             ABCvolume:             200trade_time:         2023-02-22 14:09:05closest_quote_time: 2023-02-22 14:09:00quote_price:        32.11final_price:        6422
Row 2:──────symbol:             ABCvolume:             300trade_time:         2023-02-22 14:09:28closest_quote_time: 2023-02-22 14:09:20quote_price:        32.15final_price:        9645
2 rows in set. Elapsed: 0.003 sec.

请注意，ASOF Join 的 ON 子句是必需的，它在 AND 子句的非精确匹配条件旁边指定了一个精确匹配条件。

目前，ClickHouse 不支持没有任何连接键执行严格匹配的连接（但未来可能会支持）。

▌总结 

本文介绍了 ClickHouse 支持的所有标准 SQL Join 类型以及用于支持分析查询的特殊 Join 类型。我们描述并演示了所有支持的 Join 类型。

---

作者：Tom Schreiber

更多技术干活请关注公号“云原生数据库”