MySQL 實現查詢結果交集、差集、並集的多種 SQL 寫法對比

場景

MySQL 不支持 標準 SQL 的 INTERSECT / EXCEPT（8.0 仍不支持），需用 JOIN、IN/EXISTS、UNION 組合實現集合運算。

1. 交集

方法 A：INNER JOIN（最常用、性能最好）

SELECT a.name
FROM student a
JOIN transcript t1 ON a.studid = t1.studid AND t1.crscode = 'CS305'
JOIN transcript t2 ON a.studid = t2.studid AND t2.crscode = 'CS315';

方法 B：IN / EXISTS 子查詢

SELECT name FROM student
WHERE studid IN (SELECT studid FROM transcript WHERE crscode = 'CS305')
  AND studid IN (SELECT studid FROM transcript WHERE crscode = 'CS315');

方法 C：UNION ALL + GROUP BY HAVING（通用多集合交集）

SELECT studid FROM (
    SELECT studid FROM transcript WHERE crscode = 'CS305'
    UNION ALL
    SELECT studid FROM transcript WHERE crscode = 'CS315'
) t
GROUP BY studid
HAVING COUNT(*) = 2;  -- 集合個數

2. 差集 (A - B)

方法 A：NOT IN（注意 NULL 陷阱）

SELECT * FROM table1
WHERE name NOT IN (SELECT name FROM table2 WHERE name IS NOT NULL);

方法 B：NOT EXISTS（推薦，正確處理 NULL）

SELECT * FROM table1 a
WHERE NOT EXISTS (SELECT 1 FROM table2 b WHERE b.name = a.name);

方法 C：LEFT JOIN + IS NULL

SELECT a.* FROM table1 a
LEFT JOIN table2 b ON a.name = b.name
WHERE b.name IS NULL;

3. 並集

-- 去重並集
SELECT name FROM table1
UNION
SELECT name FROM table2;

-- 全量並集（含重複，速度更快）
SELECT name FROM table1
UNION ALL
SELECT name FROM table2;

性能對比與選擇建議

實戰技巧：利用覆蓋索引加速交集

-- 建立覆蓋索引
ALTER TABLE transcript ADD INDEX idx_crs_stud (crscode, studid);

-- 查詢將走 Index Only Scan，不回表
SELECT studid FROM transcript WHERE crscode = 'CS305'
INTERSECT  -- 概念上
SELECT studid FROM transcript WHERE crscode = 'CS315';

MySQL 實際執行計劃會轉為 ref 訪問 + Using index，極快。

總結

• 交集首選 INNER JOIN，可讀性強、優化器成熟。
• 差集首選 NOT EXISTS 或 LEFT JOIN ... IS NULL，語義正確且性能穩定。
• 並集根據去重需求選 UNION / UNION ALL。
• 大數據量場景務必在關聯/過濾字段建立覆蓋索引，實現 Index Only Scan。