MySQL 實現差集 (MINUS) 與交集 (INTERSECT) 替代方案

MySQL 不支援標準 SQL 的 MINUS/EXCEPT (差集) 與 INTERSECT (交集)，但可用 JOIN / UNION ALL + GROUP BY 等效實現。

測試數據

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

交集 (INTERSECT) - 完全匹配的行

SELECT id, nickname, playNum, COUNT(*)
FROM (
  SELECT id, nickname, playNum FROM t1
  UNION ALL
  SELECT id, nickname, playNum FROM t2
) a
GROUP BY id, nickname, playNum
HAVING COUNT(*) > 1;
-- 結果：(1,1,10)

差集 (MINUS) - t1 有而 t2 無的行 (任意列不同)

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;
-- 結果：(2,2,20) (3,3,30)

原理解析

• 交集：UNION ALL 合併後分組，計數 > 1 即雙表皆有
• 差集：LEFT JOIN 保留左表，WHERE 過濾右表匹配列不等或為 NULL
• 若需「t2 有而 t1 無」，交換表順序即可