SQL 如何用 INTERSECT / EXCEPT 实现交集与差集（跨数据库兼容）

mysql 不支持 INTERSECT 和 EXCEPT，需用 INNER JOIN + DISTINCT 模拟交集、NOT EXISTS 模拟差集，并用 COALESCE 处理 NULL；union ALL + GROUP BY 方案性能差且兼容性差。

INTERSECT 和 EXCEPT 在 MySQL 中根本不能用

MySQL 8.0.31 之前完全不支持 INTERSECT 和 EXCEPT，哪怕升级到 8.0.31+，也仅在 UNION 级别语法中“名义支持”，实际执行会报错：Error 1235 (42000): this version of MySQL doesn't yet support 'select ... INTERSECT'。postgresql、SQL Server、oracle 原生支持，但写法细节有差异——比如 PostgreSQL 要求列数和类型严格一致，SQL Server 允许隐式转换但会警告。

所以跨数据库写法的第一原则是：别直接依赖这两个关键字。真要兼容 MySQL，得用 INNER JOIN + GROUP BY 模拟交集，用 LEFT JOIN ... WHERE ... IS NULL 模拟差集。

用 INNER JOIN 替代 INTERSECT（去重交集）

INTERSECT 默认去重，且要求字段结构相同。用 JOIN 模拟时，必须显式去重并处理空值。

• 如果两个查询结果都有主键或唯一组合字段，优先用 INNER JOIN + ON a.id = b.id
• 若无明确关联字段，用所有列做等值连接，例如：INNER JOIN t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
• 必须加 DISTINCT 或 GROUP BY，否则重复行会被放大（JOIN 本身不自动去重）
• NULL 值无法参与等值判断，INTERSECT 把两个 NULL 视为相等，但 = 不行，需额外用 IS NOT DISTINCT FROM（PostgreSQL 支持）或改用 COALESCE(col, '##NULL##') 临时替代（注意选一个业务中绝不会出现的占位符）

示例（兼容 MySQL/PG/SQL Server）：

SELECT DISTINCT t1.*  FROM table_a t1  INNER JOIN table_b t2    ON COALESCE(t1.name, '') = COALESCE(t2.name, '')    AND COALESCE(t1.age, -1) = COALESCE(t2.age, -1);

用 LEFT JOIN + IS NULL 替代 EXCEPT（A 减 B）

EXCEPT 返回在左表存在、右表不存在的行，同样默认去重。用 LEFT JOIN 实现时，关键点不在连接逻辑，而在「如何定义‘不存在’」。

• 不能只靠单个字段匹配，否则多列对比会漏判；必须对所有参与比较的列都做 IS NULL 判断
• 推荐用 NOT EXISTS 替代 LEFT JOIN，语义更清晰，且避免因连接产生笛卡尔积风险（尤其当右表有重复匹配时）
• MySQL 5.7 及更早版本不支持 NOT EXISTS 子查询中的聚合或窗口函数，但基础用法完全 OK
• 如果右表可能有 NULL，NOT EXISTS 天然兼容（它不依赖等值比较），而 LEFT JOIN 需手动补 NULL 处理逻辑

示例（安全通用写法）：

SELECT DISTINCT a.*  FROM table_a a  WHERE NOT EXISTS (   SELECT 1 FROM table_b b    WHERE COALESCE(a.name, '') = COALESCE(b.name, '')      AND COALESCE(a.age, -1) = COALESCE(b.age, -1) );

为什么不用 UNION ALL + GROUP BY + HAVING count = 2？

有人想用 UNION ALL 合并两结果集，再按所有列 GROUP BY + HAVING COUNT(*) = 2 来取交集。这在逻辑上看似可行，但实际踩坑密集：

• 性能极差：两表各 10 万行，合并后 20 万行再分组，远不如 JOIN 走索引快
• 无法区分 A∩B 和 B∩A 的语义（虽然数学等价，但调试时难定位来源）
• 如果某行在 A 中出现 2 次、B 中 1 次，COUNT=3 就被过滤掉——而 INTERSECT 只关心“是否存在”，不是“出现几次”
• MySQL 对 GROUP BY 非聚合字段的处理宽松，PG/SQL Server 会报错，兼容性反而更差

真正跨库稳定的方案，始终围绕“存在性判断”展开，而不是“计数归类”。复杂点在于 NULL 处理和去重控制，而不是连接方式本身。