ClickHouse跨表JOIN用startswith做关联条件报错的解决请求

ClickHouse的常规JOIN语法不支持将startswith这类函数作为ON子句的连接条件，这就是你触发INVALID_JOIN_ON_EXPRESSION错误的原因。要实现预期的关联查询效果，可以通过CROSS JOIN+过滤+UNION ALL补全无匹配记录的方式实现：

-- 获取所有匹配的记录
SELECT a.firstname, b.fullname, b.money
FROM users a
CROSS JOIN users_money b
WHERE startsWith(b.fullname, a.firstname)

UNION ALL

-- 补全没有匹配项的用户记录
SELECT firstname, '' AS fullname, 0 AS money
FROM users a
WHERE NOT EXISTS (
    SELECT 1
    FROM users_money b
    WHERE startsWith(b.fullname, a.firstname)
)
ORDER BY firstname;

执行后会返回你预期的结果：

john    johnblack   33
john    jonhwhite   48
ksenia              0
alice   aliceblack  50

另一种紧凑实现方式：数组函数组合 #

如果你偏好更简洁的写法，也可以利用数组函数完成匹配和展开：

SELECT
    firstname,
    match_tuple.1 AS fullname,
    match_tuple.2 AS money
FROM (
    SELECT
        a.firstname,
        -- 生成当前用户所有匹配的(fullname, money)元组数组
        arrayFilter(
            t -> startsWith(t.1, a.firstname),
            (SELECT groupArray(tuple(fullname, money)) FROM users_money)
        ) AS matches
    FROM users a
)
-- 展开数组，无匹配时用('', 0)填充
ARRAY JOIN if(matches != [], matches, [tuple('', 0)]) AS match_tuple
ORDER BY firstname;

这两种方式都能绕过ClickHouse对JOIN条件的限制，实现基于字符串前缀匹配的关联查询。

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