MySQL 不支援 INTERSECT / MINUS？用 JOIN 與 NOT IN/LEFT JOIN 完美替代

MySQL 至 8.0 仍不支援標準 SQL 的 INTERSECT（交集）與 MINUS/EXCEPT（差集），但可用等價改寫達到相同效果。

INTERSECT → INNER JOIN

-- 標準寫法（MySQL 不支援）
SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b;

-- 等價改寫
SELECT a.member_id, a.name
FROM a INNER JOIN b
  USING (member_id, name);

USING 要求兩表列名相同；若列名不同用 ON a.member_id = b.member_id AND a.name = b.name。INNER JOIN 天然去重（若需保留重複可用 JOIN + 條件）。

MINUS / EXCEPT → NOT IN 或 LEFT JOIN ... IS NULL

-- 標準寫法
SELECT member_id, name FROM a
MINUS
SELECT member_id, name FROM b;

-- 改寫 1：NOT IN（子查詢，MySQL 4.1+ 支援）
SELECT DISTINCT member_id, name
FROM a
WHERE (member_id, name) NOT IN (SELECT member_id, name FROM b);

-- 改寫 2：LEFT JOIN + IS NULL（經典反模式，性能通常更好）
SELECT DISTINCT a.member_id, a.name
FROM a LEFT JOIN b
  USING (member_id, name)
WHERE b.member_id IS NULL;

性能建議

• 大表差集優先用 LEFT JOIN ... IS NULL，配合被驅動表關聯列建立索引。
• NOT IN 子查詢在某些版本優化器下會退化為全表掃描，且需注意 NULL 值語義陷阱（NOT IN (..., NULL) 永為假）。
• MySQL 8.0.31+ 已支援 INTERSECT/EXCEPT，若版本允許可直接使用標準語法。

多列組合鍵注意

以上改寫均適用於多列組合鍵，只需在 USING/ON/元組比較中列出所有列。