MySQL中如何用SQL实现两个表的交集与并集？步骤是什么？

Question

在MySQL中实现两表交集与并集时，常见问题：**MySQL 5.7及更早版本不支持`INTERSECT`和`EXCEPT`（`MINUS`）操作符**，导致开发者误用`INNER JOIN`或`UNION`替代，却忽略字段顺序、NULL处理、重复行去重等细节。例如，用`UNION ALL`代替`UNION`会保留重复行，破坏并集语义；用`JOIN`模拟交集时未确保所有列值完全匹配（尤其含NULL时，`NULL = NULL`为UNKNOWN，导致漏行）。此外，两表结构需严格一致（列数、类型兼容、顺序建议一致）才能使用集合操作；若字段名不同，须显式指定别名并按位置匹配。还易忽略`DISTINCT`在`UNION`中的隐式生效机制，或误以为`INTERSECT`存在而报语法错误。正确解法是：MySQL 8.0+可用`INTERSECT`/`UNION`/`UNION ALL`；旧版本须用`INNER JOIN + GROUP BY`模拟交集，用`UNION`（自动去重）实现并集，并手动处理NULL安全比较（如`<=>`运算符）。

Answer 1

```html

一、现象层：常见报错与误用行为直击

在MySQL 5.7及更早版本中执行SELECT * FROM t1 INTERSECT SELECT * FROM t2，直接触发ERROR 1064 (42000)——语法错误。开发者常以INNER JOIN粗暴替代交集，却忽略NULL语义；或用UNION ALL代替UNION，导致并集结果含冗余行。典型误写示例：

-- ❌ 错误：未处理NULL，且未确保全字段匹配
SELECT a.* FROM t1 a INNER JOIN t2 b ON a.id = b.id AND a.name = b.name;

-- ✅ 正确（旧版）：需显式覆盖NULL安全比较
SELECT a.* FROM t1 a 
INNER JOIN t2 b 
  ON a.id <=> b.id AND a.name <=> b.name;

二、原理层：SQL集合运算的三大基石

集合操作（INTERSECT/UNION/EXCEPT）要求严格满足三性：

• 列数一致：两查询返回列数必须相等；
• 类型兼容：对应位置列应可隐式转换（如VARCHAR与TEXT）；
• 顺序敏感：按位置而非字段名匹配，SELECT name, id FROM t1与SELECT id, name FROM t2无法直接UNION。

MySQL中UNION隐式等价于UNION DISTINCT，而UNION ALL不执行去重——这是语义级差异，非性能优化选项。

三、陷阱层：NULL、重复与结构错配的深度剖析

问题类型	表现	根因
NULL漏匹配	INNER JOIN丢失含NULL的公共行	NULL = NULL返回UNKNOWN，非TRUE
字段名不一致	UNION结果列名取首个查询的别名	未显式AS统一命名，下游应用解析失败
隐式类型转换	'123' UNION 123可能触发截断或警告	字符型与数值型混合时，MySQL按CONVERT()规则隐式转为数字

四、解法层：跨版本兼容的工程化实现方案

以下为生产环境验证的稳健写法（兼容MySQL 5.6+）：

-- ✅ 交集模拟（含NULL安全）
SELECT t1.* 
FROM t1 
INNER JOIN t2 
  ON t1.id <=> t2.id 
  AND t1.status <=> t2.status 
  AND t1.updated_at <=> t2.updated_at
GROUP BY t1.id, t1.status, t1.updated_at;

-- ✅ 并集模拟（自动去重，等价UNION）
(SELECT id, status, updated_at FROM t1)
UNION
(SELECT id, status, updated_at FROM t2);

五、演进层：MySQL 8.0+原生集合运算实践指南

MySQL 8.0.19起全面支持标准集合操作，但需注意：

• INTERSECT和EXCEPT默认DISTINCT，不可加ALL修饰；
• 所有操作符优先级低于UNION，复杂表达式须用括号明确结合顺序；
• 执行计划中显示Set operation节点，可通过EXPLAIN FORMAT=TREE验证。

六、验证层：构建可复现的测试用例矩阵

使用如下数据验证各方案正确性：

CREATE TABLE t1 (id INT, name VARCHAR(10), score DECIMAL(3,1));
CREATE TABLE t2 (id INT, name VARCHAR(10), score DECIMAL(3,1));
INSERT INTO t1 VALUES (1,'Alice',85.5), (2,'Bob',NULL), (3,'Charlie',92.0);
INSERT INTO t2 VALUES (1,'Alice',85.5), (2,'Bob',90.0), (4,'David',78.5);

预期交集仅(1,'Alice',85.5)；并集应含7行（含NULL但去重后共6行+1行NULL）。

七、架构层：面向未来的集合操作抽象设计

在ORM或中间件层封装集合操作适配器，自动检测MySQL版本并路由：

graph TD
  A[调用 intersect(t1, t2)] --> B{MySQL Version >= 8.0?}
  B -->|Yes| C[生成 INTERSECT 语句]
  B -->|No| D[生成 INNER JOIN + <=> 比较]
  C --> E[执行原生命令]
  D --> F[执行兼容SQL]

八、运维层：监控与告警关键指标

在慢查询日志中重点关注：UNION子句数量＞3、JOIN条件含多个IS NULL、执行时间突增＞500ms。建议在部署检查清单中加入：

• 确认sql_mode未启用STRICT_TRANS_TABLES导致隐式转换失败；
• 检查innodb_buffer_pool_size是否足以缓存多表临时结果集；
• 对高频集合查询添加覆盖索引（如INDEX idx_union(id,name)）。

```