SQL集合运算深度解析：UNION、INTERSECT、EXCEPT操作符与应用场景

在SQL中，除了基础的增删改查，集合运算是一组强大的工具，允许我们像处理数学集合一样来操作和组合查询结果集。掌握UNION、INTERSECT和EXCEPT，能极大地提升我们从数据库中查询和组合数据的效率。

一、准备演示数据

首先，我们创建两个简单的员工表，用于后续的示例演示。

-- 创建技术部员工表
CREATE TABLE #TechDept (
    EmployeeID INT,
    Name VARCHAR(50),
    Skill VARCHAR(50)
);
-- 创建市场部员工表  
CREATE TABLE #MarketingDept (
    EmployeeID INT,
    Name VARCHAR(50),
    Skill VARCHAR(50)
);

-- 插入测试数据
INSERT INTO #TechDept VALUES
(1, '张三', 'Java'),
(2, '李四', 'Python'),
(3, '王五', 'SQL'),
(4, '赵六', 'JavaScript'),
(5, '钱七', 'Java');  -- 注意：张三和钱七都是Java技能

INSERT INTO #MarketingDept VALUES
(2, '李四', 'Python'),      -- 两个部门都有李四
(6, '孙八', 'SEO'),
(7, '周九', '广告投放'),
(3, '王五', 'PPT'),         -- 同一个王五，技能不同
(8, '吴十', '数据分析');

二、UNION：合并结果集

UNION操作用于合并两个或多个SELECT语句的结果集，并自动去除重复的行。

-- 找出所有掌握的技能（自动去重）
SELECT Skill FROM #TechDept
UNION
SELECT Skill FROM #MarketingDept
ORDER BY Skill;

执行结果：

Skill
--------------
SQL
Java
JavaScript
Python
PPT
SEO
广告投放
数据分析

核心要点：

1. 所有SELECT语句的列数必须相同。
2. 对应列的数据类型必须兼容。
3. 结果集的列名通常采用第一个SELECT语句的列名。

如果需要保留所有行（包括重复项），应使用UNION ALL。在处理多表数据时，尤其是在进行数据汇总分析时，UNION ALL的性能通常优于UNION，因为它省去了去重的开销。

-- 找出所有技能（保留重复记录）
SELECT Skill FROM #TechDept
UNION ALL
SELECT Skill FROM #MarketingDept
ORDER BY Skill;

三、INTERSECT：获取结果集的交集

INTERSECT返回两个查询结果中共同存在的行，即数学上的“交集”。

-- 找出两个部门都有的员工（根据员工ID和姓名判断）
SELECT EmployeeID, Name FROM #TechDept
INTERSECT
SELECT EmployeeID, Name FROM #MarketingDept;

执行结果：

EmployeeID  Name
----------  ------
2           李四
3           王五

更常见的业务场景是找出共同的属性，例如找出两个部门都需要的技能：

-- 找出既出现在技术部又出现在市场部的技能
SELECT Skill FROM #TechDept
INTERSECT
SELECT Skill FROM #MarketingDept;

执行结果：

Skill
-----
Python

四、EXCEPT：获取结果集的差集

EXCEPT返回存在于第一个查询结果中但不在第二个查询结果中的行，即数学上的“差集”。

-- 找出只在技术部，不在市场部的员工
SELECT EmployeeID, Name FROM #TechDept
EXCEPT
SELECT EmployeeID, Name FROM #MarketingDept;

执行结果：

EmployeeID  Name
----------  ------
1           张三
4           赵六
5           钱七

反向操作可以找出只在市场部的员工：

-- 找出只在市场部，不在技术部的员工
SELECT EmployeeID, Name FROM #MarketingDept
EXCEPT
SELECT EmployeeID, Name FROM #TechDept;

执行结果：

EmployeeID  Name
----------  ------
6           孙八
7           周九
8           吴十

五、实际业务场景应用

集合运算在真实业务逻辑中有着广泛的应用。

场景1：合并多时间段数据
常用于合并季度或月度报表。

-- 合并第一季度各月的销售数据
SELECT '1月' AS Month, Product, Sales FROM Sales_Jan
UNION ALL
SELECT '2月', Product, Sales FROM Sales_Feb
UNION ALL
SELECT '3月', Product, Sales FROM Sales_Mar
ORDER BY Product, Month;

场景2：识别用户行为变化
这对于用户增长和留存分析至关重要。

-- 找出今日新增用户（今日有，昨日无）
SELECT UserID, UserName FROM Users_Today
EXCEPT
SELECT UserID, UserName FROM Users_Yesterday;

-- 找出流失用户（昨日有，今日无）
SELECT UserID, UserName FROM Users_Yesterday
EXCEPT
SELECT UserID, UserName FROM Users_Today;

-- 找出连续活跃用户（两日均有访问）
SELECT UserID, UserName FROM Users_Today
INTERSECT
SELECT UserID, UserName FROM Users_Yesterday;

六、总结与选择指南

操作符	描述	数学等价	典型使用场景
UNION	合并结果，去除重复行	A ∪ B	合并多来源数据，且需要去重
UNION ALL	合并结果，保留所有行	A ∪ B (不去重)	高性能合并，或确认数据无重复
INTERSECT	返回两个查询共有的行	A ∩ B	寻找共同点，如共有客户、共有技能
EXCEPT	返回第一个查询有而第二个查询没有的行	A - B	寻找差异点，如新增用户、缺失数据

快速选择指南：

1. 需要合并数据时 → 考虑 UNION 或 UNION ALL。
2. 需要寻找共同点时 → 使用 INTERSECT。
3. 需要寻找不同点时 → 使用 EXCEPT。
4. 关注性能时 → 优先使用 UNION ALL。

熟练掌握这些集合操作符，能使你在处理复杂的数据对比、合并与筛选需求时更加得心应手，尤其是在进行深入的数据分析时，它们是不可或缺的利器。