使用带子查询的条件WHERE子句遇ORA-01427错误求助

你遇到的ORA-01427: single-row subquery returns more than one row错误，根源是WHERE子句里的第一个子查询(SELECT CP.COLUMN1 FROM TABLE2 CP WHERE CP.CAT='PSTATUS') IS NULL被当作单行子查询使用，但当TABLE2中存在多条CAT='PSTATUS'的记录时，这个子查询会返回多行数据，和单行判断的逻辑冲突。

下面是两种可行的解决方式：

方法一：用NOT EXISTS替换NULL判断 #

将判断子查询是否为空的逻辑改成NOT EXISTS，它天然支持多行场景：

SELECT P.* FROM TABLE1 P
WHERE NOT EXISTS (SELECT 1 FROM TABLE2 CP WHERE CP.CAT='PSTATUS')
        OR P.ID IN (SELECT CP.COLUMN1 FROM TABLE2 CP WHERE CP.CAT='PSTATUS')

NOT EXISTS会检查TABLE2中是否存在符合条件的记录，不存在时返回true，完全适配子查询可能返回多行的情况。

方法二：用聚合函数将子查询转为单行 #

如果坚持要用IS NULL判断，可以给子查询加聚合函数（比如MAX），强制让它返回单行结果：

SELECT P.* FROM TABLE1 P
WHERE (SELECT MAX(CP.COLUMN1) FROM TABLE2 CP WHERE CP.CAT='PSTATUS') IS NULL 
        OR P.ID IN (SELECT CP.COLUMN1 FROM TABLE2 CP WHERE CP.CAT='PSTATUS')

当TABLE2中没有匹配记录时，MAX函数返回NULL；有匹配时返回最大值（单行），这样就不会触发单行子查询的错误。

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