多表连接查询未选修指定课程学生的存储过程问题排查

嘿，我来帮你搞定这个存储过程的问题！首先得指出你原代码里的核心矛盾：你用了一连串的内连接（JOIN），这意味着只有在所有关联表中都能找到匹配记录的行才会被返回，但你紧接着又加了a.number is NULL的条件——内连接后a.number必然是有值的，这两个条件完全冲突，所以自然返回空结果啦。

下面给你几种更优的实现方式，都是能正确返回未选修指定课程学生的方案：

方案1：使用左连接（LEFT JOIN）筛选无匹配的学生 #

这种方法从学生表a出发，左连接到后续的选课关联表，把课程筛选条件放到左连接的ON子句里，最后筛选出没有匹配到该课程的学生：

DELIMITER //
DROP PROCEDURE IF EXISTS proc_enrollment//
CREATE PROCEDURE proc_enrollment(IN var_subjectcode VARCHAR(255))
BEGIN
    SELECT CONCAT(a.LastName, ', ', a.FirstName) AS StudentNames, a.number
    FROM a
    LEFT JOIN b ON a.number = b.number
    LEFT JOIN c ON b.number = c.number
    LEFT JOIN d ON c.stud_id = d.stud_id
    LEFT JOIN e ON d.subject_code = e.subject_code AND e.subjectcode = var_subjectcode
    WHERE e.subjectcode IS NULL;
END //
DELIMITER ;

逻辑说明：左连接会保留a表的所有学生记录，只有当学生选了指定课程时，e表的对应字段才会有值；我们筛选e.subjectcode IS NULL的行，就是那些没有选这门课的学生。

方案2：使用NOT EXISTS子查询（推荐，可读性&性能更优） #

这种方式逻辑非常直观：直接查询所有不存在于该课程选课名单中的学生，尤其是当你的表有合适索引时，性能会很好：

DELIMITER //
DROP PROCEDURE IF EXISTS proc_enrollment//
CREATE PROCEDURE proc_enrollment(IN var_subjectcode VARCHAR(255))
BEGIN
    SELECT CONCAT(a.LastName, ', ', a.FirstName) AS StudentNames, a.number
    FROM a
    WHERE NOT EXISTS (
        SELECT 1
        FROM b
        JOIN c ON b.number = c.number
        JOIN d ON c.stud_id = d.stud_id
        JOIN e ON d.subject_code = e.subject_code
        WHERE b.number = a.number
          AND e.subjectcode = var_subjectcode
    );
END //
DELIMITER ;

逻辑说明：子查询会找出所有选了指定课程的学生，外层查询则返回不在这个名单里的学生，完全符合你的需求。

方案3：使用NOT IN子查询（需注意空值问题） #

如果你的数据中b.number不会出现NULL值，可以用这种方式，先获取所有选了该课程的学生编号，再筛选不在这个列表里的学生：

DELIMITER //
DROP PROCEDURE IF EXISTS proc_enrollment//
CREATE PROCEDURE proc_enrollment(IN var_subjectcode VARCHAR(255))
BEGIN
    SELECT CONCAT(a.LastName, ', ', a.FirstName) AS StudentNames, a.number
    FROM a
    WHERE a.number NOT IN (
        SELECT b.number
        FROM b
        JOIN c ON b.number = c.number
        JOIN d ON c.stud_id = d.stud_id
        JOIN e ON d.subject_code = e.subject_code
        WHERE e.subjectcode = var_subjectcode
    );
END //
DELIMITER ;

注意：如果子查询返回的b.number包含NULL，NOT IN会直接返回空结果，所以只有确认数据无空值时才推荐用这个方法。

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