如何处理ClickHouse SQL中的INVALID_JOIN_ON_EXPRESSION错误并实现等价高效SQL？

问题原因 #

你提供的PostgreSQL左连接代码在ClickHouse中报错，核心原因是ClickHouse默认限制JOIN的ON子句只能包含等值匹配条件，不支持直接在ON中写入>=、<这类范围判断——这是ClickHouse基于哈希等值匹配的JOIN优化机制决定的。

你的需求是保留table1所有行，仅当ID匹配且t1.date落在t2.valid_from_date与t2.valid_to_date区间时关联t2数据，以下是两种高效等价写法：

方法1：使用LATERAL LEFT JOIN（推荐，完全匹配原逻辑） #

ClickHouse支持LATERAL关联，允许子查询直接引用主表字段，完美复现原PostgreSQL的区间关联逻辑，同时保留table1所有行：

SELECT *
FROM table1 t1
LATERAL LEFT JOIN (
    SELECT *
    FROM table2 t2
    WHERE t2.id = t1.id 
      AND t1.date >= t2.valid_from_date 
      AND t1.date < t2.valid_to_date
) t2

这种写法会对t1的每一行，在t2中精准筛选出符合ID匹配+区间条件的行，和原PostgreSQL代码的行为完全一致（包括返回多个匹配的t2行）。

方法2：等值JOIN后过滤（适合单区间匹配场景） #

如果t2中每个id仅对应一个时间区间，可以先做等值LEFT JOIN，再通过条件过滤将不符合区间的t2字段置为NULL：

SELECT
    t1.*,
    -- 按需求逐一处理t2字段，仅保留符合区间条件的数据
    IF(t1.date >= t2.valid_from_date AND t1.date < t2.valid_to_date, t2.column1, NULL) AS t2_column1,
    IF(t1.date >= t2.valid_from_date AND t1.date < t2.valid_to_date, t2.column2, NULL) AS t2_column2,
    IF(t1.date >= t2.valid_from_date AND t1.date < t2.valid_to_date, t2.valid_from_date, NULL) AS valid_from_date,
    IF(t1.date >= t2.valid_from_date AND t1.date < t2.valid_to_date, t2.valid_to_date, NULL) AS valid_to_date
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id

注意：如果t2中一个id对应多个区间，这种写法会返回重复的t1行，建议改用方法1。

性能优化建议 #

• 给table2的id字段设置为排序键（ORDER BY）或主键（PRIMARY KEY），加速ID匹配过滤
• 若t2数据量极大，可按id分区，进一步缩小查询范围
• 频繁做区间查询时，给t2的valid_from_date和valid_to_date设置区间索引（Range Index）

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