如何将NOT IN查询转为OUTER JOIN优化？求教导师课程查询方案

嘿，我完全懂你为啥想把NOT IN换成OUTER JOIN——尤其是涉及中间表的时候，NOT IN在处理大数据集或者存在NULL值时，经常会出现性能瓶颈，甚至返回意外结果。咱们针对你的场景一步步来实现转换。

首先先明确下咱们涉及的表结构（根据你的描述补全了合理的字段，要是和你的实际表有出入，调整字段名就行）：

• tutors：存储导师基础信息，字段大概是tutor_id（主键）、full_name（全名）、email（邮箱）
• unittutor：中间关联表，字段是tutor_id、unit_id（关联导师和课程单元）
• units：存储课程单元信息，字段是unit_id（主键）、year（年份）、semester（学期）

先看原来的NOT IN写法（作为对比） #

这应该是你最初可能会写的查询：

SELECT t.full_name, t.email
FROM tutors t
WHERE t.tutor_id NOT IN (
    SELECT ut.tutor_id
    FROM unittutor ut
    JOIN units u ON ut.unit_id = u.unit_id
    WHERE u.year = 2018 AND u.semester = 2
);

转成LEFT OUTER JOIN的优化写法 #

咱们把子查询改成两次LEFT JOIN，核心是把学期筛选条件放在JOIN的ON子句里，而不是WHERE里，这样才能保留所有导师的记录，再过滤掉匹配到目标学期课程的行：

SELECT t.full_name, t.email
FROM tutors t
LEFT JOIN unittutor ut 
    ON t.tutor_id = ut.tutor_id
LEFT JOIN units u 
    ON ut.unit_id = u.unit_id 
    AND u.year = 2018 
    AND u.semester = 2
WHERE u.unit_id IS NULL;

关键逻辑解释 #

1. 第一次LEFT JOIN：把tutors和unittutor关联，保证所有导师都被包含进来，不管他们有没有教过任何课程
2. 第二次LEFT JOIN带筛选条件：关联units时，直接把2018年第2学期的条件写在ON里——这一步很重要！如果把条件放在WHERE里，就会变成INNER JOIN的效果，直接过滤掉没教过课的导师
3. WHERE过滤：u.unit_id IS NULL表示这个导师在units里找不到匹配的2018年第2学期的课程，也就是咱们要找的目标导师

额外优化：处理重复记录 #

如果一个导师教过多个该学期的课程，LEFT JOIN可能会返回重复的导师记录，这时候可以用DISTINCT去重：

SELECT DISTINCT t.full_name, t.email
FROM tutors t
LEFT JOIN unittutor ut 
    ON t.tutor_id = ut.tutor_id
LEFT JOIN units u 
    ON ut.unit_id = u.unit_id 
    AND u.year = 2018 
    AND u.semester = 2
WHERE u.unit_id IS NULL;

或者用GROUP BY（需要保证tutor_id是主键，这样分组不会丢失信息）：

SELECT t.full_name, t.email
FROM tutors t
LEFT JOIN unittutor ut 
    ON t.tutor_id = ut.tutor_id
LEFT JOIN units u 
    ON ut.unit_id = u.unit_id 
    AND u.year = 2018 
    AND u.semester = 2
WHERE u.unit_id IS NULL
GROUP BY t.tutor_id, t.full_name, t.email;

最后再提一句：如果你的unittutor.tutor_id、unittutor.unit_id、units.unit_id这些关联字段没有索引，记得加上，这会让JOIN的效率提升很多！

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