Clickhouse的关联子查询:Join引擎

关于Clickhouse 的子查询

Clkhouse 支持非关联主表的子查询语句，如下

SELECT `number` AS  number1,
(SELECT number FROM numbers(3) WHERE number=2) AS number2 ,
(SELECT number FROM numbers(3) WHERE number=5) AS number3 
FROM test2 a LIMIT 10;

number1	number2	number3
0	2
1	2
2	2
3	2
4	2
5	2
6	2
7	2
8	2
9	2

不支持像Oracle 一样的 两表需要关联查询的子查询语句。比如

但是，Clikhouse可以通过Join引擎来实现此类关联子查询。

Join 引擎（关联表引擎）

一般语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
) ENGINE = Join(join_strictness, join_type, k1[, k2, ...])

参见官方文档：https://clickhouse.com/docs/zh/engines/table-engines/special/join
使用该引擎，即可实现关联子查询。举例如下：

SELECT *FROM test1 LIMIT 10;

number	number1	number2
0	1	0
1	2	2
2	3	4
3	4	6
4	5	8
5	6	10
6	7	12
7	8	14
8	9	16
9	10	18

CREATE TABLE test1_joinner engine=Join(ANY,LEFT,number)
AS 
SELECT number,number1,number2 
FROM test1;

SELECT `number` AS  number 
 ,joinGet('test1_joinner','number1',a.number) AS number1
 ,joinGet('test1_joinner','number2',a.number) AS number2
FROM test2 a LIMIT 10;

number	number1	number2
0	1	0
1	2	2
2	3	4
3	4	6
4	5	8
5	6	10
6	7	12
7	8	14
8	9	16
9	10	18

关于Join引擎和left join语句的效率问题

在我的实际工作过程中，400万的数据，使用join引擎子查询和left join 语句查询，耗费的时间完全一样。
测试语句较为简单，仅是两个表的左外关联。