Oracle SQL如何简化「值在列表中或列表为空」的条件写法？

核心逻辑等价转换 #

原需求的过滤规则可直接推导等价逻辑，完全消除重复子查询：
原OR组合条件：

(:some_value 为 null) OR (:some_value 在关联子查询结果中) OR (关联子查询结果为空)
会被排除的唯一场景是：some_value非空 + 关联子查询有结果 + some_value不在子查询结果中，该场景等价于存在关联上的子查询行，且行中匹配字段不等于some_value。
因此可直接把三个OR条件简化为单个NOT EXISTS判断，完全符合DRY原则，且兼容所有Oracle版本：

:some_value IS NULL 
OR NOT EXISTS (
    SELECT 1 FROM cte 
    WHERE table1.id = cte.id1 
    AND cte.idX != :some_value
)

优化后的完整可运行示例 #

对应提供的SQL*Plus测试用例，优化后代码如下：

create table table1 as select 1 id, 'one' name , 12 price from dual
                 union select 2   , 'two'      , 22       from dual
                 union select 3   , 'thr'      , 33       from dual;

create table table2 as select 1 id1, 88 idX, sysdate-1 validDate from dual -- 有效数据
                 union select 1    , 99    , sysdate+2           from dual -- 未到生效时间
                 union select 2    , 99    , sysdate+3           from dual;

var some_value number
-- exec :some_value  := 88  -- 取消注释测试非空参数场景

with cte as (select id1,idX from table2 where validDate<sysdate)
select * from table1
where
    table1.price  > 10
    AND (
        :some_value IS NULL 
        OR NOT EXISTS (
            SELECT 1 FROM cte 
            WHERE table1.id = cte.id1 
            AND cte.idX != :some_value
        )
    );

其他可选方案（Oracle 12c+支持） #

如果需要保留子查询的其他返回值做额外逻辑，可使用LATERAL关联一次性查出关联结果的统计信息，避免重复查询：

with cte as (select id1,idX from table2 where validDate<sysdate)
select t1.* 
from table1 t1
LEFT JOIN LATERAL (
    SELECT 
        COUNT(*) match_total,
        MAX(CASE WHEN idX = :some_value THEN 1 ELSE 0 END) has_match_value
    FROM cte WHERE cte.id1 = t1.id
) t2 ON 1=1
where
    t1.price >10
    AND (
        :some_value IS NULL
        OR t2.match_total = 0
        OR t2.has_match_value = 1
    );

内容的提问来源于stack exchange，提问作者David Balažic