Databricks中SQL报错：标量子查询需仅返回单一列

报错原因 #

你遇到的[INVALID_SUBQUERY_EXPRESSION.SCALAR_SUBQUERY_RETURN_MORE_THAN_ONE_OUTPUT_COLUMN]错误，核心原因是子查询外层多余的括号导致Databricks SQL误判查询类型：

• 你使用了多列匹配的(col1, col2, col3) IN (子查询)语法，但子查询被额外套了两层括号((select ... intersect select ...))
• 多余的外层括号让引擎把整个子查询当成了标量子查询（要求仅返回1列），而不是返回多列行集合的普通子查询，因此触发“返回3列不符合要求”的报错

另外原SQL还有一个语法错误：select all.a, all.b, all.c中的all并非有效别名，你没有给查询结果或表指定这个别名，应该改用实际表别名（比如a11）。

修正后的SQL #

去掉IN子查询外层多余的括号，同时修正别名错误：

select 
    a11.a,
    a11.b,
    a11.c
from    com a11
    join    fam_grp a12
      on    ((a11.mkt_id || a11.brd_id) = (a12.mkt_id || a12.brd_id) and 
    a11.dataset = a12.dataset)
where   (a11.cust_id, a11.mkt_id, a11.o_cust_id)
 in (
    select   pc21.cust_id, pc21.mkt_id_1, pc21.o_cust_id
    from    ZZMD00  pc21
    group by    pc21.cust_id, pc21.mkt_id_1, pc21.o_cust_id
    intersect 
    select  pc21.cust_id, pc21.mkt_id_1, pc21.o_cust_id
    from    ZZMD01  pc21
    group by    pc21.cust_id, pc21.mkt_id_1, pc21.o_cust_id
)
 and a11.dataset in ('C')
group by    
    a11.a,
    a11.b,
    a11.c

额外优化建议 #

1. 移除冗余的GROUP BY：INTERSECT运算符本身会自动去重，除非ZZMD00/ZZMD01中存在重复的(cust_id, mkt_id_1, o_cust_id)组合需要提前过滤，否则可以去掉子查询里的GROUP BY，简化语句。
2. 改用EXISTS提升性能：多列匹配场景下，EXISTS子查询有时会比IN有更好的执行效率，改写示例：

select 
    a11.a,
    a11.b,
    a11.c
from    com a11
    join    fam_grp a12
      on    ((a11.mkt_id || a11.brd_id) = (a12.mkt_id || a12.brd_id) and 
    a11.dataset = a12.dataset)
where   exists (
    select 1
    from ZZMD00 pc21
    where pc21.cust_id = a11.cust_id 
      and pc21.mkt_id_1 = a11.mkt_id 
      and pc21.o_cust_id = a11.o_cust_id
    intersect
    select 1
    from ZZMD01 pc22
    where pc22.cust_id = a11.cust_id 
      and pc22.mkt_id_1 = a11.mkt_id 
      and pc22.o_cust_id = a11.o_cust_id
)
 and a11.dataset in ('C')
group by    
    a11.a,
    a11.b,
    a11.c

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