Oracle父子表查询优化：筛选无Pending状态子记录的参考编号

问题描述 #

我有父表SI_DETAIL和子表SI_TRANSDETAIL，父表的参考编号S_NO在子表中对应多条记录，状态分为Pending、Success和Rejected。需要编写查询语句，仅返回那些子表记录仅包含Success和Rejected状态、无任何Pending状态的S_NO；只要某S_NO的子表存在至少一条Pending记录，就不列出该编号。

表结构及测试数据 #

CREATE TABLE SI_DETAIL
  ( "S_NO" VARCHAR2(20 BYTE),
    "CREATED_DATE" DATE);
    
CREATE TABLE SI_TRANSDETAIL
  ( "S_NO"  VARCHAR2(20 BYTE),
    "SL_NO" VARCHAR2(20 BYTE),
    "EXE_DATE" DATE,
    "STATUS" VARCHAR2(20 BYTE));
    
Insert into SI_DETAIL (S_NO,CREATED_DATE) values ('1000',to_date('29-11-22','DD-MM-RR'));
Insert into SI_DETAIL (S_NO,CREATED_DATE) values ('1001',to_date('01-12-22','DD-MM-RR'));
Insert into SI_DETAIL (S_NO,CREATED_DATE) values ('1002',to_date('30-11-22','DD-MM-RR'));

Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1000','1',to_date('30-11-22','DD-MM-RR'),'REJECTED');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1000','2',to_date('01-12-22','DD-MM-RR'),'SUCCESS');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1000','3',to_date('02-12-22','DD-MM-RR'),'SUCCESS');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1001','1',to_date('02-12-22','DD-MM-RR'),'SUCCESS');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1001','2',to_date('03-12-22','DD-MM-RR'),'PENDING');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1001','3',to_date('04-12-22','DD-MM-RR'),'PENDING');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1001','4',to_date('05-12-22','DD-MM-RR'),'PENDING');
Insert into SI_TRANSDETAIL (S_NO,SL_NO,EXE_DATE,STATUS) values ('1002','1',to_date('04-12-22','DD-MM-RR'),'PENDING');

我目前使用以下查询能得到期望结果（仅返回S_NO=1000），请问是否有更优的解决方案？

SELECT S_NO FROM SI_TRANSDETAIL DTL
WHERE  EXISTS (SELECT S_NO FROM SI_DETAIL HD
               WHERE  DTL.S_NO = HD.S_NO  
                      AND CREATED_DATE < SYSDATE - 1
                      AND HD.S_NO NOT IN (SELECT S_NO 
                                          FROM   SI_TRANSDETAIL SUB 
                                          WHERE  STATUS = 'PENDING' 
                                                 AND SUB.S_NO = HD.S_NO));

优化方案 #

方案1：用NOT EXISTS直接过滤Pending记录 #

这种写法逻辑直观，避免嵌套NOT IN（NOT IN遇到NULL会出现意外结果，NOT EXISTS更可靠），同时从父表出发贴合业务需求：

SELECT DISTINCT HD.S_NO
FROM SI_DETAIL HD
WHERE HD.CREATED_DATE < SYSDATE - 1
  AND NOT EXISTS (
    SELECT 1
    FROM SI_TRANSDETAIL SUB
    WHERE SUB.S_NO = HD.S_NO
      AND SUB.STATUS = 'PENDING'
  )
  AND EXISTS (
    -- 可选：确保父表S_NO在子表中有对应记录，若父表无关联子表的S_NO不需要保留则加上
    SELECT 1
    FROM SI_TRANSDETAIL SUB
    WHERE SUB.S_NO = HD.S_NO
  );

优势：逻辑清晰，直接排除含Pending记录的S_NO，DISTINCT保证结果唯一，避免子表多记录导致重复输出。

方案2：分组聚合验证状态 #

通过分组统计Pending记录的数量，一次分组完成过滤，扩展性更强（如需额外统计Success/Rejected数量可直接添加逻辑）：

SELECT HD.S_NO
FROM SI_DETAIL HD
JOIN (
    SELECT S_NO
    FROM SI_TRANSDETAIL
    GROUP BY S_NO
    HAVING COUNT(CASE WHEN STATUS = 'PENDING' THEN 1 END) = 0
) SUB ON HD.S_NO = SUB.S_NO
WHERE HD.CREATED_DATE < SYSDATE - 1;

优势：减少关联次数，适合需要同时处理子表状态统计的场景。

方案3：LEFT JOIN过滤 #

通过左关联Pending记录，筛选关联结果为NULL的S_NO，适合习惯用JOIN写法的场景：

SELECT DISTINCT HD.S_NO
FROM SI_DETAIL HD
JOIN SI_TRANSDETAIL SUB ON HD.S_NO = SUB.S_NO
LEFT JOIN SI_TRANSDETAIL P_SUB ON HD.S_NO = P_SUB.S_NO AND P_SUB.STATUS = 'PENDING'
WHERE HD.CREATED_DATE < SYSDATE - 1
  AND P_SUB.S_NO IS NULL;

优势：关联关系可视化，Oracle优化器对JOIN的处理通常更高效。

对比原查询的优势 #

原查询从子表出发会导致重复输出S_NO（需额外去重），且多层嵌套的NOT IN可读性差。上述优化方案：

1. 从父表出发，逻辑更贴合“查找符合条件的父表编号”的业务需求
2. 避免多层嵌套子查询，可读性更强
3. 性能更优，减少不必要的关联和子查询执行次数

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