在SQL Server中，集合运算是一种强大的工具，用于组合和比较两个或多个查询的结果集。本文将深入探讨UNION, EXCEPT和INTERSECT这三种集合运算，并通过实例代码来阐述它们的功能和用法。
UNION操作符用于合并两个查询的结果集，同时去除重复的行。在以下示例中，我们创建了两个派生表，然后使用UNION将它们合并，最终得到[a, b, c, d, e]的结果：
```sql
SELECT FC FROM (VALUES('a'),('b'),('c'),('e')) Table1 (FC)
UNION
SELECT FC FROM (VALUES('a'),('b'),('c'),('d')) Table2 (FC)
```
如果想要保留重复的行，可以使用UNION ALL，它不会执行任何去重操作。例如：
```sql
SELECT * FROM (VALUES('a','Anna'),('b','Bob'),('c','Cassie'),('e','Elina')) Table1 (FC, Name)
UNION ALL
SELECT * FROM (VALUES('a','Anna'),('b','Bob'),('c','Cassie'),('d','David')) Table2 (FC, Name)
```
接着是EXCEPT操作符，它返回只在第一个查询结果集中出现而不在第二个结果集中出现的行。例如：
```sql
SELECT FC FROM (VALUES('a'),('b'),('c'),('e')) Table1 (FC)
EXCEPT
SELECT FC FROM (VALUES('a'),('b'),('c'),('d')) Table2 (FC)
```
这个例子中，结果是[e]，因为'e'只在Table1中出现。
再来看INTERSECT操作符，它返回两个查询结果集的交集，即同时存在于两个结果集中的行：
```sql
SELECT FC FROM (VALUES('a'),('b'),('c'),('e')) Table1 (FC)
INTERSECT
SELECT FC FROM (VALUES('a'),('b'),('c'),('d')) Table2 (FC)
```
结果是[a, b, c]，这三个元素在两个表中都存在。
集合运算在实际应用中有多种场景。例如，UNION经常用来替代WHERE子句中的OR条件，以提高查询效率。下面展示了如何将两个OR条件转换为UNION：
```sql
-- 使用WHERE子句 + OR
SELECT name, population, area FROM world WHERE area > 3000000 OR population > 25000000
-- 使用UNION
SELECT name, population, area FROM world WHERE area > 3000000
UNION
SELECT name, population, area FROM world WHERE population > 25000000
```
另外，EXCEPT和INTERSECT可用于过滤数据。比如，你可以找出数据库中不存在的客户ID或仅存在于数据库中的客户ID。假设我们有一个名为Customers的表，包含如下数据：
```sql
-- 示例数据
CREATE TABLE Customers (
cust_id VARCHAR(20),
cust_name VARCHAR(50),
cust_address VARCHAR(100),
cust_city VARCHAR(50),
cust_state VARCHAR(50),
cust_country VARCHAR(50),
cust_contact VARCHAR(50),
cust_email VARCHAR(100)
);
INSERT INTO Customers VALUES
('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', 'USA', 'John Smith', 'sales@villagetoys.com'),
('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', 'USA', 'Michelle Green', NULL),
('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', 'USA', 'Jim Jones', 'jjones@fun4all.com'),
('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com'),
('1000000005', 'The ToyStore', '4545 53rd Street', 'Chicago', 'IL', 'USA', 'Kim Howard', NULL);
```
我们可以用EXCEPT找出不在数据库中的客户ID：
```sql
-- 过滤出列表中不存在于数据库中的项
SELECT [Id] AS [cust_id] FROM(
VALUES('1000000004'), ('1000000005'), ('1000000006')
) List (Id)
EXCEPT
SELECT cust_id FROM Customers
```
同样，我们可以使用INTERSECT找到存在于数据库中的客户ID：
```sql
-- 过滤出列表中存在于数据库中的项
SELECT [Id] AS [cust_id] FROM(
VALUES('1000000004'), ('1000000005'), ('1000000006'), ('1000000007')
) List (Id)
INTERSECT
SELECT cust_id FROM Customers
```
总结，SQL Server中的UNION, EXCEPT和INTERSECT是数据查询和分析的重要工具。它们帮助开发者有效地合并和比较数据，实现复杂的查询需求。理解并熟练运用这些集合运算，能够提升数据库管理的效率和质量。在实际工作中，根据业务场景选择合适的集合运算，可以简化问题，提高代码的可读性和性能。
