SQL Server 集合操作详解：UNION、UNION ALL、EXCEPT 和 INTERSECT
SQL Server 提供了四种常见的集合操作：UNION、UNION ALL、EXCEPT 和 INTERSECT。这些操作符可以用于结合多个查询的结果集，并进行相关的集合运算。通过这些操作，我们可以方便地合并、排除或求交两个查询的结果。
本文将详细介绍每个集合操作符的使用方式、语法和示例，并分析它们的特点和应用场景。
1. UNION 操作
UNION 操作符用于合并两个或多个查询的结果集，并去除重复的记录。它的工作原理是将各查询的结果集行合并，并自动执行去重操作。
语法：
SELECT 列名 FROM 表名1
UNION
SELECT 列名 FROM 表名2;
使用规则：
- 列数和列类型：所有查询的列数和列的数据类型必须相同或兼容。
- 去重：
UNION会自动去除重复的行，返回的结果集中每一行是唯一的。
示例：
假设我们有两个表：Employees 和 Contractors，它们都包含员工和承包商的 EmployeeID 和 Name 字段。现在我们要查询所有员工和承包商的姓名，并去除重复项。
SELECT EmployeeID, Name FROM Employees
UNION
SELECT ContractorID AS EmployeeID, Name FROM Contractors;
解释：
- 我们使用
UNION合并了Employees表和Contractors表的姓名，并通过去重确保每个姓名只出现一次。
应用场景：
- 当需要从多个表中获取相同类型的记录，并且不希望重复数据出现在最终结果中时，使用
UNION很有用。
2. UNION ALL 操作
UNION ALL 操作符与 UNION 类似，不同的是它不会去重。它返回所有结果，包括重复的行。使用 UNION ALL 比 UNION 更高效，因为不需要执行去重操作。
语法：
SELECT 列名 FROM 表名1
UNION ALL
SELECT 列名 FROM 表名2;
使用规则：
- 列数和列类型：与
UNION相同，所有查询的列数和列类型必须相同或兼容。 - 不去重：
UNION ALL会保留所有结果，包括重复行。
示例：
使用同样的 Employees 和 Contractors 表，我们现在要查询所有员工和承包商的姓名，并包括重复项。
SELECT EmployeeID, Name FROM Employees
UNION ALL
SELECT ContractorID AS EmployeeID, Name FROM Contractors;
解释：
UNION ALL合并了Employees表和Contractors表的记录，不会去掉重复的行，所有的记录都会保留在结果集中。
应用场景：
- 当需要合并多个查询结果并保留所有记录时，使用
UNION ALL。例如，合并多个月份的销售数据时，保留所有记录，包括重复的销售记录。
3. EXCEPT 操作
EXCEPT 操作符用于返回第一个查询结果中存在的行，而在第二个查询结果中没有出现的行。也就是说，它会排除第二个查询结果中的所有记录，只返回第一个查询结果中的独特行。
语法：
SELECT 列名 FROM 表名1
EXCEPT
SELECT 列名 FROM 表名2;
使用规则：
- 列数和列类型：所有查询的列数和列类型必须相同或兼容。
- 去重：
EXCEPT会去除重复行，只保留在第一个查询结果中存在，而在第二个查询结果中没有出现的行。
示例：
假设我们要查询在 Employees 表中存在但不在 Contractors 表中的所有员工。
SELECT EmployeeID, Name FROM Employees
EXCEPT
SELECT ContractorID AS EmployeeID, Name FROM Contractors;
解释：
- 使用
EXCEPT返回所有在Employees表中存在但不在Contractors表中的员工。可以看作是一个集合的差集操作。
应用场景：
- 当需要查找一个集合中不包含在另一个集合中的记录时，使用
EXCEPT。例如，查找所有没有完成任务的员工，或从一个列表中排除掉已经处理的数据。
4. INTERSECT 操作
INTERSECT 操作符用于返回两个查询结果中的交集，即两个查询中都存在的行。它返回的是两个查询结果中都出现的记录。
语法：
SELECT 列名 FROM 表名1
INTERSECT
SELECT 列名 FROM 表名2;
使用规则：
- 列数和列类型：所有查询的列数和列类型必须相同或兼容。
- 去重：
INTERSECT会去除重复行，只返回两个查询结果的交集。
示例：
假设我们要查询同时在 Employees 和 Contractors 表中出现的所有姓名。
SELECT EmployeeID, Name FROM Employees
INTERSECT
SELECT ContractorID AS EmployeeID, Name FROM Contractors;
解释：
- 使用
INTERSECT返回的是同时存在于Employees表和Contractors表中的员工信息。
应用场景：
- 当需要查找两个集合中共有的记录时，使用
INTERSECT。例如，查找在两个列表中都出现过的产品，或找到同时满足两个条件的数据。
5. UNION 和 UNION ALL 的性能比较
在性能方面，UNION 会进行去重操作，这意味着 SQL Server 会执行更多的计算，尤其是当数据量较大时。UNION ALL 不进行去重，因此它通常比 UNION 更高效。UNION 适用于需要去重的情况，而 UNION ALL 适用于不关心重复的情况。
示例：
-- 使用 UNION
SELECT Name FROM Employees
UNION
SELECT Name FROM Contractors;
-- 使用 UNION ALL
SELECT Name FROM Employees
UNION ALL
SELECT Name FROM Contractors;
如果数据集较大，UNION ALL 会比 UNION 更快，因为它跳过了去重操作。
6. EXCEPT 和 INTERSECT 的性能比较
EXCEPT 和 INTERSECT 都是集合运算符，执行时需要计算两个查询结果的差集或交集。它们在性能上通常较慢，尤其是在大数据集的情况下，因为需要对比两个查询结果中的所有记录。
示例：
-- 使用 EXCEPT
SELECT Name FROM Employees
EXCEPT
SELECT Name FROM Contractors;
-- 使用 INTERSECT
SELECT Name FROM Employees
INTERSECT
SELECT Name FROM Contractors;
在大数据量下，EXCEPT 和 INTERSECT 的性能相对较差。如果没有必要使用去重功能，应该尽量避免使用这两个操作符。
总结
SQL Server 提供的集合操作符 UNION、UNION ALL、EXCEPT 和 INTERSECT，为我们在查询和数据处理过程中提供了强大的工具，能够有效地帮助我们合并、排除或求交多个查询结果。根据实际需求选择合适的集合操作符，可以大大提高查询的效率和准确性。
UNION：合并多个查询结果并去除重复行。UNION ALL：合并多个查询结果并保留所有记录，包括重复行。EXCEPT：返回在第一个查询结果中存在但在第二个查询结果中没有的记录。INTERSECT：返回两个查询结果的交集，即同时存在于两个查询中的记录。
理解和掌握这些集合操作符的使用方法，可以帮助我们处理复杂的查询需求，并提高数据库操作的灵活性和效率。