萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 不支援 EXCEPT?用 NOT IN / LEFT JOIN 替代「not exists (B except A)」

問題來源

教科書範例: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))。

實戰範例:找出修完 Biology 系所有課程的學生

測試資料

student(id, name, dept)
takes(student_id, course_id, ...)
course(course_id, title, dept_name)

錯誤寫法(含 EXCEPT)

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)
);

正確寫法一:NOT IN 子查詢

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 課程數 = 該系總課程數。

正確寫法二:LEFT JOIN + IS NULL(效能通常更好)

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 系課程」且「該學生未修讀」的組合。

正確寫法三:EXCEPT 替代方案(MySQL 8.0+ 支援 EXCEPT)

-- 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
);

效能比較建議

  1. 小資料量:NOT IN 可讀性佳。
  2. 大資料量、有索引:LEFT JOIN ... IS NULL 或 NOT EXISTS 雙層嵌套,優化器可用 Anti-Join。
  3. MySQL 8.0+:直接用 EXCEPT,語意最清晰,優化器處理成熟。

驗證結果

-- Biology 系共 3 門課,僅 Tanaka 修畢
+-------+--------+
| id    | name   |
+-------+--------+
| 98988 | Tanaka |
+-------+--------+
copyright © 萬盛學電腦網 all rights reserved