教科書範例:WHERE NOT EXISTS ( (SELECT course_id FROM course WHERE dept='Biology') EXCEPT (SELECT course_id FROM takes WHERE student_id=S.id) )
MySQL 不支援 EXCEPT 集合運算,執行報錯 ERROR 1064。
NOT EXISTS (B EXCEPT A) 等價於 「B 中每一個元素都在 A 中出現」,即 B ⊆ A。
SQL 寫法:NOT EXISTS (SELECT 1 FROM B WHERE b.key NOT IN (SELECT key FROM A))。
student(id, name, dept)
takes(student_id, course_id, ...)
course(course_id, title, dept_name)
SELECT S.id, S.name FROM student S
WHERE NOT EXISTS (
(SELECT course_id FROM course WHERE dept_name='Biology')
EXCEPT
(SELECT T.course_id FROM takes T WHERE T.student_id = S.id)
);
SELECT DISTINCT S.id, S.name
FROM student S
JOIN takes T ON S.id = T.student_id
WHERE T.course_id IN (SELECT course_id FROM course WHERE dept_name='Biology')
GROUP BY S.id, S.name
HAVING COUNT(DISTINCT T.course_id) = (SELECT COUNT(*) FROM course WHERE dept_name='Biology');
邏輯:學生修的 Biology 課程數 = 該系總課程數。
SELECT S.id, S.name
FROM student S
WHERE NOT EXISTS (
SELECT 1 FROM course C
WHERE C.dept_name = 'Biology'
AND NOT EXISTS (
SELECT 1 FROM takes T
WHERE T.student_id = S.id AND T.course_id = C.course_id
)
);
邏輯:不存在「Biology 系課程」且「該學生未修讀」的組合。
-- MySQL 8.0 可直接用 EXCEPT
SELECT S.id, S.name FROM student S
WHERE NOT EXISTS (
SELECT course_id FROM course WHERE dept_name='Biology'
EXCEPT
SELECT course_id FROM takes WHERE student_id=S.id
);
NOT IN 可讀性佳。LEFT JOIN ... IS NULL 或 NOT EXISTS 雙層嵌套,優化器可用 Anti-Join。EXCEPT,語意最清晰,優化器處理成熟。-- Biology 系共 3 門課,僅 Tanaka 修畢
+-------+--------+
| id | name |
+-------+--------+
| 98988 | Tanaka |
+-------+--------+