Azure Databricks中CASE内IN子查询报错，寻求可行替代方案

Azure Databricks SQL引擎限制IN/EXISTS类型的子查询只能用于Filter或Join场景，CASE表达式内的这类子查询会触发逐行执行，因此抛出错误。以下是实现相同逻辑的替代方案：

方案一：LEFT JOIN关联表生成标记列 #

通过左关联目标表，利用关联结果是否非空来判断id是否存在，替代原CASE内的IN子查询：

SELECT 
    t.id,
    CASE WHEN a.id IS NOT NULL THEN 1 ELSE 0 END AS a,
    CASE WHEN b.id IS NOT NULL THEN 1 ELSE 0 END AS b,
    CASE WHEN c.id IS NOT NULL THEN 1 ELSE 0 END AS c
FROM table t
LEFT JOIN aTable a ON t.id = a.id
LEFT JOIN bTable b ON t.id = b.id
LEFT JOIN cTable c ON t.id = c.id

方案二：去重后关联（避免重复行） #

如果关联表中存在重复id，可先对关联表去重再关联，防止主表id被重复输出：

SELECT 
    t.id,
    CASE WHEN a.id IS NOT NULL THEN 1 ELSE 0 END AS a,
    CASE WHEN b.id IS NOT NULL THEN 1 ELSE 0 END AS b,
    CASE WHEN c.id IS NOT NULL THEN 1 ELSE 0 END AS c
FROM table t
LEFT JOIN (SELECT DISTINCT id FROM aTable) a ON t.id = a.id
LEFT JOIN (SELECT DISTINCT id FROM bTable) b ON t.id = b.id
LEFT JOIN (SELECT DISTINCT id FROM cTable) c ON t.id = c.id

内容的提问来源于stack exchange，提问作者J. Doe