SQL CASE语句中如何编写判断子查询无返回结果的条件

报错原因 #

你触发的Scalar subquery produced more than one element报错，本质是因为你写的子查询会返回多行/多列结果：

1. SELECT *会返回tableB的所有字段，不是单个值
2. 当tableB中存在多条满足innerField = outerTable.field的记录时，子查询会返回多行结果
   这类非标量的结果不能直接用IS NULL做判断。

最优实现方案 #

推荐用NOT EXISTS关键字实现「判断子查询无匹配记录」的逻辑，这是SQL原生支持的存在性判断语法，性能和兼容性都最优：

UPDATE tableA AS outerTable
SET field = CASE 
                WHEN NOT EXISTS (
                         -- 此处用SELECT 1即可，不需要查询实际字段，性能更高
                         SELECT 1 
                         FROM tableB 
                         WHERE innerField = outerTable.field
                     )
                THEN (
                        -- 无子查询匹配时的赋值逻辑写此处
                     ) 
                ELSE 'default'
            END 

其他可选实现方案 #

如果你需要结合子查询返回的结果做计算判断，可以用聚合函数保证子查询返回标量值：

UPDATE tableA AS outerTable
SET field = CASE 
                WHEN (
                         SELECT COUNT(*)
                         FROM tableB 
                         WHERE innerField = outerTable.field
                     ) = 0
                THEN '无匹配时的赋值'
                ELSE 'default'
            END 

注意事项 #

• EXISTS/NOT EXISTS的子查询建议写SELECT 1替代SELECT *，数据库不需要加载实际字段数据，查询效率更高
• 如果业务逻辑需要使用子查询返回的具体值，需要用LIMIT 1或者MAX()/MIN()等聚合函数，保证子查询只返回单个值
• 上述写法兼容所有主流关系型数据库（MySQL、PostgreSQL、Oracle、SQL Server等）

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