如何在ClickHouse中使用Between非等值条件实现表连接？

ClickHouse 默认的 JOIN 语法仅支持等值连接，所以你用 a.dt between b.start_dt and b.end_dt 这种非等值条件会触发 Code: 403 错误。以下是几种实现该逻辑的可行方案：

方案1：子查询关联（适合单条匹配场景） #

如果表 a 的每条记录在表 b 中最多对应一条匹配的区间记录，可以用子查询直接获取对应字段：

select 
    a.*, 
    (select name from b where a.dt between b.start_dt and b.end_dt limit 1) as name
from a;

如果存在多条匹配，可改用聚合函数（如 any(name)）确保返回单值：

select 
    a.*, 
    (select any(name) from b where a.dt between b.start_dt and b.end_dt) as name
from a;

方案2：CROSS JOIN + WHERE 过滤（小表场景适用） #

通过笛卡尔积生成所有组合后，再用WHERE条件过滤出符合区间的记录。注意：仅适合两张表数据量都较小的场景，数据量大时性能极差：

select a.*, b.name
from a cross join b
where a.dt between b.start_dt and b.end_dt;

方案3：Interval Join（推荐时间区间场景） #

ClickHouse 提供了专门的 interval join 语法，针对时间区间类的非等值连接做了性能优化，是这类场景的最优解：

select a.*, b.name
from a 
interval join b 
on a.dt between b.start_dt and b.end_dt;

注意事项：

• 要求表 b 的时间区间不重叠；如果存在重叠区间，需要先对 b 表做去重或合并处理。
• 该特性需要 ClickHouse 19.17 及以上版本支持。

额外优化建议 #

• 若使用子查询方案，建议给表 b 的 start_dt 和 end_dt 字段建立索引，加快区间匹配的查询速度。
• 如果需要处理多对多的区间匹配，可结合 arrayJoin 将匹配结果转为数组后展开，避免返回重复的主表数据。

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