SQL中WHERE IN子查询无结果时如何替换为另一子查询的实现方案

你原来用CASE的思路不可行，因为CASE是标量表达式，仅能返回单个值，无法满足IN关键字需要的多行结果集合要求。可以直接通过条件分支组合的方式改写WHERE逻辑，写法如下：

WHERE 
    -- 当存在对应settingId的配置记录时，匹配setting表的fieldId
    (
        EXISTS (SELECT 1 FROM setting WHERE settingId = joinedTable.settingId)
        AND fieldId IN (SELECT fieldId FROM setting WHERE settingId = joinedTable.settingId)
    )
    -- 当不存在对应settingId的配置记录时， fallback匹配所有field表的id
    OR 
    (
        NOT EXISTS (SELECT 1 FROM setting WHERE settingId = joinedTable.settingId)
        AND fieldId IN (SELECT id FROM field)
    )

性能优化建议 #

如果你的数据量较大，担心子查询重复执行带来性能损耗，也可以通过预关联的方式减少子查询次数，注意如果单个settingId对应多条fieldId记录，关联可能会产生冗余行，需要配合DISTINCT或者改写为EXISTS逻辑适配：

SELECT DISTINCT main_table.*
FROM main_table
INNER JOIN joinedTable ON main_table.join_key = joinedTable.join_key
-- 预关联setting表的对应记录
LEFT JOIN setting ON setting.settingId = joinedTable.settingId
WHERE 
    (setting.settingId IS NOT NULL AND main_table.fieldId = setting.fieldId)
    OR 
    (setting.settingId IS NULL AND main_table.fieldId IN (SELECT id FROM field))

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