ClickHouse：如何将外部数组与表执行Left Join？求优化方案

关于直接用数组左连接的可行性 #

你想要的这种外部数组和表左连接的思路完全可行，但ClickHouse不支持直接写FROM ['id1', 'id2', 'id3-missing']这种语法，需要用正确的方式构造临时输入表来实现。

最优替代方案 #

针对你的场景（表数据和输入规模都极小，需支持聚合），推荐两种简单高效的实现方式：

方案1：用arrayJoin构造输入临时表

这是最灵活的方式，适合输入是数组的场景：

SELECT input.id, d.detail_one, d.detail_two
FROM (
    SELECT arrayJoin(['id1', 'id2', 'id3-missing']) AS id
) AS input
LEFT JOIN details d ON d.id = input.id

执行后会得到你预期的结果：

┌─id───────────┬─detail_one─┬─detail_two─┐
│ id1          │          5 │         10 │
│ id2          │         20 │         30 │
│ id3-missing  │       NULL │       NULL │
└──────────────┴────────────┴────────────┘

方案2：用VALUES子句构造输入临时表

如果输入元素是明确的离散值，用这种方式更直观：

SELECT input.id, d.detail_one, d.detail_two
FROM (
    VALUES ('id1'), ('id2'), ('id3-missing')
) AS input(id)
LEFT JOIN details d ON d.id = input.id

效果和方案1完全一致，同样能返回缺失id对应的NULL值。

为什么这两种方案比你尝试过的更好 #

• WHERE IN只能筛选出主表中存在的id，无法获取输入数组里不存在于主表的id记录；
• 基于输入数组的UNION方案需要手动为缺失id补全NULL值，不仅麻烦，还不灵活（输入数组变动时要修改SQL）；
• 上面的两种方案直接生成包含所有输入id的临时表，左连接后自动得到缺失id的NULL值，无需额外的哈希映射处理，还能直接在此基础上执行聚合操作。

聚合操作示例 #

比如要统计每个输入id的detail_one总和（缺失id用0填充）：

SELECT 
    input.id, 
    COALESCE(SUM(d.detail_one), 0) AS total_detail_one
FROM (
    SELECT arrayJoin(['id1', 'id2', 'id3-missing']) AS id
) AS input
LEFT JOIN details d ON d.id = input.id
GROUP BY input.id

执行结果：

┌─id───────────┬─total_detail_one─┐
│ id1          │                5 │
│ id2          │               20 │
│ id3-missing  │                0 │
└──────────────┴──────────────────┘

内容的提问来源于stack exchange，提问作者Mikhail Krassavin