使用 Join 的 SQL Not IN 查询

Question

我如何使用左连接编写以下查询这是我的示例查询

SELECT * 
FROM STUDENT st 
WHERE st.stdid not IN 
   (    SELECT csch.stid 
        FROM courseSchedule csch,    section sctn 
        WHERE cschno.sectno = sctn.sectno 
             AND cschno.college= sctn.college
             AND sctn.college = st.college AND sctn.year = 2013 and sctn.semester = 'spring' 
     )
     AND st.college = 1

Answer 1

要将 NOT IN 子查询移动到 LEFT JOIN，请在 NOT IN 键标准上执行左连接，然后检查右表 KEY 是否为 NULL。请注意，您还可以将子查询中的 WHERE 连接转换为 ANSI 连接:

SELECT st.*
  FROM STUDENT st 
    LEFT JOIN courseSchedule csch 
      ON st.stdid = csch.stid 
    INNER JOIN section sctn 
      ON cschno.sectno = sctn.sectno 
     AND cschno.college= sctn.college
     AND sctn.college = st.college 
     AND sctn.year = 2013 
     AND sctn.semester = 'spring'
  WHERE st.college = 1
     AND csch.stid IS NULL; -- "Not IN"