sql中的并(UNION)、交(INTERSECT)、差(MINUS)

这三个操作是 SQL 中处理“数据集之间关系”的核心本质是对两个查询结果集做集合运算类似数学中的并集、交集、差集核心要求两个结果集的字段数、字段类型、字段顺序必须完全一致。先准备两张结构完全相同的测试表方便示例理解-- 表A一班学生CREATETABLEclass_a(idINT,nameVARCHAR(20));INSERTINTOclass_aVALUES(1,张三),(2,李四),(3,王五);-- 表B二班学生和一班有重叠CREATETABLEclass_b(idINT,nameVARCHAR(20));INSERTINTOclass_bVALUES(2,李四),(3,王五),(4,赵六);一、并UNION / UNION ALL合并两个结果集1. 核心定义UNION合并两个结果集自动去重删除重复行UNION ALL合并两个结果集保留所有行包括重复性能更高优先用。2. 语法与示例-- 1. UNION去重合并一班和二班学生重复的只保留一次SELECTid,nameFROMclass_aUNIONSELECTid,nameFROMclass_b;-- 结果4行去重/* id | name 1 | 张三 2 | 李四 3 | 王五 4 | 赵六 */-- 2. UNION ALL不去重保留所有行包括重复的李四、王五SELECTid,nameFROMclass_aUNIONALLSELECTid,nameFROMclass_b;-- 结果6行保留重复/* id | name 1 | 张三 2 | 李四 3 | 王五 2 | 李四 3 | 王五 4 | 赵六 */3. 关键注意事项必须保证两个查询的字段数、类型、顺序一致如不能一个查2字段一个查3字段性能UNION ALL无需去重性能远高于UNION能确定无重复时优先用 UNION ALL排序可在最后加ORDER BY对合并后的结果排序示例SELECTid,nameFROMclass_aUNIONALLSELECTid,nameFROMclass_bORDERBYidDESC;二、交INTERSECT取两个结果集的交集1. 核心定义返回两个结果集中同时存在的行即“既在A里又在B里”类似数学中的交集。2. 语法与注意事项⚠️MySQL 原生不支持 INTERSECT 关键字需用IN/EXISTS/JOIN模拟PostgreSQL、Oracle 等支持。方式1用 JOIN 模拟推荐性能优-- 取一班和二班的共同学生李四、王五SELECTa.*FROMclass_a aJOINclass_b bONa.idb.idANDa.nameb.name;-- 结果/* id | name 2 | 李四 3 | 王五 */方式2用 IN 模拟简单适合小表SELECT*FROMclass_aWHERE(id,name)IN(SELECTid,nameFROMclass_b);方式3用 EXISTS 模拟适合大表SELECT*FROMclass_a aWHEREEXISTS(SELECT1FROMclass_b bWHEREa.idb.idANDa.nameb.name);三、差MINUS / EXCEPT取两个结果集的差集1. 核心定义返回“在A里但不在B里”的行左差集或“在B里但不在A里”的行右差集。MINUSOracle 关键字EXCEPTPostgreSQL、SQL Server 关键字⚠️MySQL 原生不支持 MINUS/EXCEPT需用LEFT JOIN IS NULL模拟。2. 语法与示例示例1左差集仅在一班不在二班-- 取“只在一班”的学生张三SELECTa.*FROMclass_a aLEFTJOINclass_b bONa.idb.idANDa.nameb.nameWHEREb.idISNULL;-- 结果/* id | name 1 | 张三 */示例2右差集仅在二班不在一班-- 取“只在二班”的学生赵六SELECTb.*FROMclass_a aRIGHTJOINclass_b bONa.idb.idANDa.nameb.nameWHEREa.idISNULL;-- 结果/* id | name 4 | 赵六 */四、核心对比表操作含义MySQL 支持情况核心特点UNION并集去重原生支持自动去重性能稍差UNION ALL并集不去重原生支持保留所有行性能最优INTERSECT交集不支持需用 JOIN/IN/EXISTS 模拟仅返回共同行MINUS/EXCEPT差集不支持需用 LEFT JOIN 模拟仅返回“只在A/只在B”的行五、避坑关键字段一致性所有集合操作必须保证两个结果集的字段数、类型、顺序完全一致否则报错NULL 值处理NULL 不参与集合匹配如NULL NULL为 false需用IS NULL单独判断性能优先并集优先用UNION ALL而非UNION交集/差集大表用JOIN/EXISTS小表用INMySQL 替代方案记牢“交集用 JOIN差集用 LEFT JOIN IS NULL”这是 MySQL 中最通用的写法。总结UNION/UNION ALL合并结果集UNION 去重、UNION ALL 保留所有行性能优MySQL 原生支持INTERSECT交集取共同行MySQL 需用 JOIN/IN/EXISTS 模拟MINUS/EXCEPT差集取“仅在A/仅在B”的行MySQL 需用 LEFT JOIN IS NULL 模拟核心前提所有集合操作的两个结果集必须字段数、类型、顺序一致。这些集合操作是 SQL 数据分析的常用工具尤其在报表统计、数据对比场景中高频使用重点掌握 MySQL 的替代实现方式即可。