含WHERE NOT IN子句的SQL查询未达预期的原因分析

问题背景 #

需要找出安静学生：至少参加过一次考试，且所有考试中既没拿过最高分也没拿过最低分的学生（未参加考试的学生排除）。现有SQL查询未返回预期的student_id=2的Jade，需分析原因。

输入表结构 #

Student表 #

Exam表 #

异常SQL查询 #

with student_scores as (
select student_id, student_name, 
rank() over (partition by exam_id order by score asc) worse_rank,
rank() over (partition by exam_id order by score desc) best_rank

from Student
inner join Exam using (student_id)
)

select distinct student_id, student_name
from student_scores
where student_id not in (

select student_id
from student_scores
where ((worse_rank=1) or (best_rank=1)) and (student_id is not null)
)

预期返回student_id=2的Jade，但实际无结果。

调试观察到的现象 #

• 新增带DISTINCT的CTEloud，并在NOT IN中引用该CTE，查询正常返回结果
• 若student_id被显式定义为PRIMARY KEY，原查询正常工作
• 用student_name替代student_id放在NOT IN子句中，原查询正常工作
• 直接在NOT IN后的子查询中加DISTINCT无法解决问题

原因分析 #

核心问题出在**NOT IN子查询返回重复的student_id值，且SQL优化器的处理逻辑导致异常**：

1. NOT IN与重复值的隐式处理：
   当子查询返回重复的同一student_id时，部分SQL引擎会在执行NOT IN时出现逻辑判断偏差。重复值会让引擎在匹配时误以为存在不确定的匹配逻辑，最终导致整个NOT IN条件返回UNKNOWN，从而过滤掉所有结果。

2. 主键约束的影响：
   当student_id是PRIMARY KEY时，优化器明确知道子查询返回的student_id是唯一的，不会有重复值，因此会正确处理NOT IN逻辑，避免了重复值带来的判断异常。

3. student_name替代的原因：
   当前数据中student_name是唯一的，相当于天然的唯一值集合，因此NOT IN处理时不会遇到重复值导致的逻辑问题，查询正常。

4. 直接在子查询加DISTINCT无效的原因：
   部分SQL优化器会将子查询的DISTINCT与外层查询逻辑合并，没有真正将子查询结果去重后再进行NOT IN判断。而单独用CTE加DISTINCT，会强制优化器先计算出唯一的student_id集合，再进行匹配，因此能得到正确结果。

解决方案 #

除了调试中提到的方法，还可以用以下更简洁的方式：

方法1：用NOT EXISTS替代NOT IN #

NOT EXISTS对重复值的处理更稳定，不会出现NOT IN的逻辑异常：

with student_scores as (
select student_id, student_name, 
rank() over (partition by exam_id order by score asc) worse_rank,
rank() over (partition by exam_id order by score desc) best_rank
from Student
inner join Exam using (student_id)
)
select distinct s.student_id, s.student_name
from student_scores s
where not exists (
    select 1
    from student_scores s2
    where s2.student_id = s.student_id
    and (s2.worse_rank = 1 or s2.best_rank = 1)
)

方法2：提前聚合学生的考试极值情况 #

先统计每个学生是否拿过最高分或最低分，再筛选：

with exam_extremes as (
    select exam_id, 
           min(score) as min_score, 
           max(score) as max_score
    from Exam
    group by exam_id
),
student_extreme_check as (
    select distinct e.student_id,
           case when e.score = ee.min_score or e.score = ee.max_score then 1 else 0 end as is_loud
    from Exam e
    join exam_extremes ee on e.exam_id = ee.exam_id
)
select s.student_id, s.student_name
from Student s
join student_extreme_check sec on s.student_id = sec.student_id
group by s.student_id, s.student_name
having max(sec.is_loud) = 0

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