WHERE子句CASE搭配IN触发ORA-01427错误 不使用UNION如何改写SQL

报错原因 #

ORA-01427报错的根因是CASE属于单行标量表达式，要求每个分支的返回结果都是单个值，你在非指定时段的分支中写的子查询会返回多条白名单COLUMN_A值，不符合单行要求，因此触发报错。

改写方案（不使用UNION） #

直接将两个业务场景的过滤逻辑拆分到WHERE子句中，通过逻辑运算符组合即可：

SELECT
    *
FROM
    MY_TABLE
WHERE
    -- 指定小时段直接放行所有记录
    TO_CHAR(COL_TIMESTAMP, 'YYYYMMDDHH24') = '2021111217'
    OR
    -- 其余时段仅放行白名单内的记录
    (
        TO_CHAR(COL_TIMESTAMP, 'YYYYMMDDHH24') != '2021111217'
        AND EXISTS (
            SELECT 1
            FROM ANOTHER_TABLE
            WHERE ANOTHER_TABLE.COLUMN_A = MY_TABLE.COLUMN_A
        )
    )

可选性能优化 #

如果COL_TIMESTAMP列建有索引，建议把时间判断的函数写法替换为范围判断，避免索引失效：

SELECT
    *
FROM
    MY_TABLE
WHERE
    -- 时间范围判断可以触发COL_TIMESTAMP的索引，查询效率更高
    (
        COL_TIMESTAMP >= TO_DATE('2021-11-12 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
        AND COL_TIMESTAMP < TO_DATE('2021-11-12 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
    )
    OR
    (
        (
            COL_TIMESTAMP < TO_DATE('2021-11-12 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
            OR COL_TIMESTAMP >= TO_DATE('2021-11-12 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
        )
        AND EXISTS (
            SELECT 1
            FROM ANOTHER_TABLE
            WHERE ANOTHER_TABLE.COLUMN_A = MY_TABLE.COLUMN_A
        )
    )

内容的提问来源于stack exchange，提问作者Omari Victor Omosa