MySQL 實現差集與交集：UNION ALL + GROUP BY 模擬 INTERSECT/EXCEPT 實戰

MySQL 實現差集與交集：UNION ALL + GROUP BY 模擬 INTERSECT/EXCEPT 實戰

MySQL 8.0 前不支援 INTERSECT/EXCEPT（MINUS），本文提供標準 SQL 替代方案，經測試驗證可直接用於生產環境。

一、測試資料準備

DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (id INT PRIMARY KEY, nickname VARCHAR(20), playNum VARCHAR(20));
CREATE TABLE t2 (id INT PRIMARY KEY, nickname VARCHAR(20), playNum VARCHAR(20));

INSERT INTO t1 VALUES (1,1,10), (2,2,20), (3,3,30);
INSERT INTO t2 VALUES (1,1,10), (2,2,200), (3,33,300);

預期結果：

• 交集：id=1, nickname=1, playNum=10（完全相同行）；
• 差集：t1 獨有 → id=2 (playNum 不同)、id=3 (nickname/playNum 皆不同)。

二、交集（INTERSECT）實現

SELECT id, nickname, playNum, COUNT(*) AS cnt
FROM (
    SELECT id, nickname, playNum FROM t1
    UNION ALL
    SELECT id, nickname, playNum FROM t2
) a
GROUP BY id, nickname, playNum
HAVING COUNT(*) > 1;

原理： UNION ALL 保留重複行，分組計數 >1 即為兩表共同擁有的完全相同行。

三、差集（EXCEPT / MINUS）實現

SELECT t1.id, t1.nickname, t1.playNum
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.nickname != t2.nickname OR t1.playNum != t2.playNum;

原理： 左連接保留 t1 所有行，WHERE 條件過濾掉 t2 中完全匹配的行，剩餘即為 t1 獨有或不匹配行。

四、擴展：不依賴主鍵的通用寫法

-- 交集（無主鍵）
SELECT * FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id=t1.id AND t2.nickname=t1.nickname AND t2.playNum=t1.playNum);

-- 差集（無主鍵）
SELECT * FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id=t1.id AND t2.nickname=t1.nickname AND t2.playNum=t1.playNum);

適用於無主鍵、複合唯一鍵或全行比對場景，效能視索引情況而定。

五、MySQL 8.0+ 原生支援

-- 交集
SELECT * FROM t1 INTERSECT SELECT * FROM t2;

-- 差集
SELECT * FROM t1 EXCEPT SELECT * FROM t2;  -- 或 MINUS

升級至 8.0 後可直接使用標準語法，優化器自動選擇最佳執行計畫。