为何Query 3无输出？硬编码ID正常的SQL问题排查

现有三个SQL查询：

• Query 1通过窗口函数可确认Jade从未取得任何考试的最高分或最低分；
• Query 2能查询出所有取得过高分或低分的student_id，其中Jade的ID不在列表内；
• 但Query 3使用NOT IN子查询筛选从未得过高/低分的学生时无输出，手动硬编码Query 2的ID却能得到包含Jade的正确结果。

请求解释该现象原因，并提供更优实现方案。

原始查询语句 #

Query 1 #

WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        student JOIN exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
) SELECT
        *
    FROM cte;

通过该查询可确认：Jade从未拿到过任何考试的最高分或最低分。

Query 2 #

WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        student JOIN exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
) SELECT
        DISTINCT student_id
    FROM cte
    WHERE
        score = max_score OR score = min_score;

通过该查询可确认：Jade的student_id不在高分/低分获得者列表中。

Query 3 #

WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        student JOIN exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
) SELECT
    DISTINCT student_id, student_name
    FROM cte
    WHERE
        student_id NOT IN ( SELECT DISTINCT student_id
                            FROM cte
                            WHERE score = max_score OR score = min_score );

疑问：为什么Query 3没有输出？但手动把Query 2得到的ID硬编码进去，就能得到包含Jade的正确结果？

原始表结构与数据 #

CREATE TABLE IF NOT EXISTS Student (student_id INT, student_name VARCHAR(30));
CREATE TABLE IF NOT EXISTS Exam (exam_id INT, student_id INT, score INT);

INSERT INTO Student (student_id, student_name) VALUES (1, 'Daniel');
INSERT INTO Student (student_id, student_name) VALUES (2, 'Jade');
INSERT INTO Student (student_id, student_name) VALUES (3, 'Stella');
INSERT INTO Student (student_id, student_name) VALUES (4, 'Jonathan');
INSERT INTO Student (student_id, student_name) VALUES (5, 'Will');

INSERT INTO Exam (exam_id, student_id, score) VALUES (10, 1, 70);
INSERT INTO Exam (exam_id, student_id, score) VALUES (10, 2, 80);
INSERT INTO Exam (exam_id, student_id, score) VALUES (10, 3, 90);
INSERT INTO Exam (exam_id, student_id, score) VALUES (20, 1, 80);
INSERT INTO Exam (exam_id, student_id, score) VALUES (30, 1, 70);
INSERT INTO Exam (exam_id, student_id, score) VALUES (30, 3, 80);
INSERT INTO Exam (exam_id, student_id, score) VALUES (30, 4, 90);
INSERT INTO Exam (exam_id, student_id, score) VALUES (40, 1, 60);
INSERT INTO Exam (exam_id, student_id, score) VALUES (40, 2, 70);
INSERT INTO Exam (exam_id, student_id, score) VALUES (40, 4, 80);

1. 无效的CTE ORDER BY干扰执行计划 #

Query 3的CTE中添加了ORDER BY exam_id, student_id，但该子句在无LIMIT配合的情况下是无效的——大多数SQL数据库会忽略CTE定义中的ORDER BY（除非用于分页）。这种无效的排序指令可能干扰数据库优化器的执行计划，导致子查询与主查询对CTE数据的处理出现异常，最终无法匹配到Jade的记录。

2. NOT IN的NULL陷阱（潜在风险） #

虽然本次案例中子查询结果无NULL，但NOT IN的逻辑特性需要注意：如果子查询结果包含NULL值，value NOT IN (...)会返回UNKNOWN（因为SQL中value != NULL始终为UNKNOWN），导致所有行被过滤。这是NOT IN的常见坑点，也是推荐用NOT EXISTS替代的原因。

方案一：用窗口函数直接标记学生是否得过最值 #

该方案通过窗口函数一次性统计每个学生是否有过最值记录，逻辑清晰且效率更高，还能包含未参加考试的学生：

WITH student_exam_stats AS (
    SELECT 
        s.student_id,
        s.student_name,
        -- 统计该学生是否有过任何一次考试的最值
        MAX(CASE WHEN score = MAX(score) OVER (PARTITION BY exam_id) 
                  OR score = MIN(score) OVER (PARTITION BY exam_id) 
             THEN 1 ELSE 0 END) OVER (PARTITION BY s.student_id) AS has_extreme_score
    FROM student s
    LEFT JOIN exam e ON s.student_id = e.student_id
)
SELECT student_id, student_name
FROM student_exam_stats
WHERE has_extreme_score = 0 OR has_extreme_score IS NULL;
-- has_extreme_score IS NULL对应未参加任何考试的学生

方案二：用NOT EXISTS替代NOT IN #

避免NOT IN的NULL陷阱，同时逻辑更直观：

WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM student 
    JOIN exam USING (student_id)
)
SELECT DISTINCT c.student_id, c.student_name
FROM cte c
WHERE NOT EXISTS (
    SELECT 1
    FROM cte c2
    WHERE c2.student_id = c.student_id
    AND (c2.score = c2.max_score OR c2.score = c2.min_score)
);

方案三：修复原Query 3（去掉无效ORDER BY） #

如果坚持使用原逻辑，只需去掉CTE中的无效ORDER BY即可：

WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        student JOIN exam
        USING (student_id)
) SELECT
    DISTINCT student_id, student_name
    FROM cte
    WHERE
        student_id NOT IN ( SELECT DISTINCT student_id
                            FROM cte
                            WHERE score = max_score OR score = min_score );

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