Oracle嵌套SELECT查询结果不符问题求助及语句修正

核心错误 #

你的嵌套查询逻辑完全搞反了字段匹配：

• 子查询返回的是LOCATIONID（21个唯一值）
• 外层查询却用COURSESESSIONID去匹配这些LOCATIONID，这两个字段是完全不同的业务标识，自然会出现结果条数不符的情况。

修正后的查询 #

把外层查询的匹配字段改成cs.LOCATIONID，就能拿到对应LOCATIONID的所有COURSESESSION数据：

select * 
from COURSESESSION cs 
where cs.LOCATIONID in (
    select distinct c.LOCATIONID 
    from COURSESESSION c 
    where c.MARKETID=280 
      and c.STATUSID in(1, 2)
      and c.COURSESESSIONID not in (SELECT distinct REGISTRATION.COURSESESSIONID FROM REGISTRATION)
);

优化建议 #

1. 去除多余的distinct #

Oracle在in子查询中会自动去重，所以子查询里的distinct可以省略，减少不必要的运算：

select * 
from COURSESESSION cs 
where cs.LOCATIONID in (
    select c.LOCATIONID 
    from COURSESESSION c 
    where c.MARKETID=280 
      and c.STATUSID in(1, 2)
      and c.COURSESESSIONID not in (SELECT REGISTRATION.COURSESESSIONID FROM REGISTRATION)
);

2. 用NOT EXISTS替代NOT IN（更安全） #

如果REGISTRATION.COURSESESSIONID存在NULL值，NOT IN会直接返回空结果，而NOT EXISTS不受NULL影响，逻辑更可靠：

select * 
from COURSESESSION cs 
where cs.LOCATIONID in (
    select c.LOCATIONID 
    from COURSESESSION c 
    where c.MARKETID=280 
      and c.STATUSID in(1, 2)
      and not exists (
          select 1 
          from REGISTRATION r 
          where r.COURSESESSIONID = c.COURSESESSIONID
      )
);

3. 用JOIN方式重构（性能更优） #

对于大数据量场景，JOIN通常比嵌套子查询效率更高，也更直观：

select cs.*
from COURSESESSION cs
join (
    select distinct c.LOCATIONID
    from COURSESESSION c
    left join REGISTRATION r on c.COURSESESSIONID = r.COURSESESSIONID
    where c.MARKETID=280 
      and c.STATUSID in(1, 2)
      and r.COURSESESSIONID is null
) valid_loc on cs.LOCATIONID = valid_loc.LOCATIONID;

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