SQL-集合运算示例与查询结果(UNION、INTERSECT、EXCEPT等)

在SQL Server中，集合运算用于操作两个或多个查询的结果集，生成新的结果集。主要的集合运算有：

用法和作用

UNION（并集）

• 用法：

  SELECT column1, column2 FROM table1
  UNION
  SELECT column1, column2 FROM table2;

• 作用：
• 合并两个查询的结果集。
• 自动去除重复行。
• 注意事项：
• 两个查询的列数必须相同。
• 对应列的数据类型必须兼容。

UNION ALL（并集，保留重复行）

• 用法：

  SELECT column1, column2 FROM table1
  UNION ALL
  SELECT column1, column2 FROM table2;

• 作用：
• 合并两个查询的结果集。
• 保留重复行。
• 注意事项：
• 与UNION相比，性能可能更好，因为不需要去重。

INTERSECT（交集）

• 用法：

  SELECT column1, column2 FROM table1
  INTERSECT
  SELECT column1, column2 FROM table2;

• 作用：
• 返回两个查询结果集中共有的行。
• 注意事项：
• 两个查询的列数必须相同。
• 对应列的数据类型必须兼容。

EXCEPT（差集）

• 用法：

  SELECT column1, column2 FROM table1
  EXCEPT
  SELECT column1, column2 FROM table2;

• 作用：
• 返回在第一个查询结果集中有，但在第二个查询结果集中没有的记录。
• 注意事项：
• 两个查询的列数必须相同。
• 对应列的数据类型必须兼容。

示例

假设有一个Students表和一个Teachers表，都包含Name和Age列。

• UNION示例：

  SELECT Name, Age FROM Students
  UNION
  SELECT Name, Age FROM Teachers;
  -- 结果：所有学生和教师的姓名和年龄，去除重复项。

• UNION ALL示例：

  SELECT Name, Age FROM Students
  UNION ALL
  SELECT Name, Age FROM Teachers;
  -- 结果：所有学生和教师的姓名和年龄，包括重复项。

• INTERSECT示例：

  SELECT Name, Age FROM Students
  INTERSECT
  SELECT Name, Age FROM Teachers;
  -- 结果：既是学生又是教师的人员的姓名和年龄。

• EXCEPT示例：

  SELECT Name, Age FROM Students
  EXCEPT
  SELECT Name, Age FROM Teachers;
  -- 结果：只在学生表中，不在教师表中的人员的姓名和年龄。

通过集合运算，可以方便地对多个结果集进行组合、比较和筛选，实现复杂的数据查询和分析。

示例

下面我将提供各种集合运算的完整示例，包括创建测试表的SQL语句、查询语句以及预期的查询结果。

1. 准备测试数据

-- 创建员工表（IT部门）
CREATE TABLE IT_Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Position VARCHAR(50)
);

-- 创建员工表（研发部门）
CREATE TABLE RND_Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Position VARCHAR(50)
);

-- 插入测试数据
INSERT INTO IT_Employees VALUES 
(1, '张三', '高级工程师'),
(2, '李四', '项目经理'),
(3, '王五', '系统架构师'),
(4, '赵六', '数据库管理员');

INSERT INTO RND_Employees VALUES 
(2, '李四', '研发经理'),
(3, '王五', '首席科学家'),
(5, '钱七', '算法工程师'),
(6, '孙八', '机器学习专家');

2. UNION 示例

-- 合并两个部门的员工名单（去重）
SELECT EmployeeID, EmployeeName FROM IT_Employees
UNION
SELECT EmployeeID, EmployeeName FROM RND_Employees
ORDER BY EmployeeID;

查询结果：

EmployeeID	EmployeeName
1	张三
2	李四
3	王五
4	赵六
5	钱七
6	孙八

3. UNION ALL 示例

-- 合并两个部门的员工名单（保留重复）
SELECT EmployeeID, EmployeeName FROM IT_Employees
UNION ALL
SELECT EmployeeID, EmployeeName FROM RND_Employees
ORDER BY EmployeeID;

查询结果：

EmployeeID	EmployeeName
1	张三
2	李四
2	李四
3	王五
3	王五
4	赵六
5	钱七
6	孙八

4. INTERSECT 示例

-- 找出同时在IT和研发部门的员工
SELECT EmployeeID, EmployeeName FROM IT_Employees
INTERSECT
SELECT EmployeeID, EmployeeName FROM RND_Employees;

查询结果：

EmployeeID	EmployeeName
2	李四
3	王五

5. EXCEPT 示例

-- 找出只在IT部门不在研发部门的员工
SELECT EmployeeID, EmployeeName FROM IT_Employees
EXCEPT
SELECT EmployeeID, EmployeeName FROM RND_Employees;

查询结果：

EmployeeID	EmployeeName
1	张三
4	赵六

6. 复杂集合运算示例

示例1：多表集合运算

-- 创建第三个表（离职员工表）
CREATE TABLE Former_Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    LastPosition VARCHAR(50)
);

INSERT INTO Former_Employees VALUES 
(1, '张三', '初级工程师'),
(7, '周九', '测试工程师');

-- 找出当前在职但不在研发部门的员工
(SELECT EmployeeID, EmployeeName FROM IT_Employees
 UNION
 SELECT EmployeeID, EmployeeName FROM RND_Employees)
EXCEPT
SELECT EmployeeID, EmployeeName FROM Former_Employees
EXCEPT
SELECT EmployeeID, EmployeeName FROM RND_Employees;

查询结果：

EmployeeID	EmployeeName
4	赵六

示例2：带WHERE和ORDER BY的集合运算

-- 合并两个部门的管理层员工并按姓名排序
SELECT EmployeeName, Position, 'IT' AS Department 
FROM IT_Employees 
WHERE Position LIKE '%经理%' OR Position LIKE '%架构师%'
UNION
SELECT EmployeeName, Position, 'R&D' AS Department 
FROM RND_Employees 
WHERE Position LIKE '%经理%' OR Position LIKE '%科学家%'
ORDER BY EmployeeName;

查询结果：

EmployeeName	Position	Department
李四	项目经理	IT
李四	研发经理	R&D
王五	系统架构师	IT
王五	首席科学家	R&D
钱七	算法工程师	R&D

7. 实际业务场景示例

场景：电商产品分析

-- 创建测试表
CREATE TABLE Current_Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Category VARCHAR(30),
    Price DECIMAL(10,2)
);

CREATE TABLE Discontinued_Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Category VARCHAR(30),
    LastStock INT
);

-- 插入测试数据
INSERT INTO Current_Products VALUES
(1, '智能手机X', '电子', 5999.00),
(2, '无线耳机', '电子', 399.00),
(3, '运动水杯', '家居', 59.00),
(4, '背包', '服饰', 199.00);

INSERT INTO Discontinued_Products VALUES
(2, '无线耳机', '电子', 12),
(5, '机械键盘', '电子', 0),
(6, '保温杯', '家居', 8);

-- 查询1：所有产品（当前+下架）的去重列表
SELECT ProductID, ProductName, Category FROM Current_Products
UNION
SELECT ProductID, ProductName, Category FROM Discontinued_Products
ORDER BY Category, ProductName;

查询结果：

ProductID	ProductName	Category
2	无线耳机	电子
1	智能手机X	电子
5	机械键盘	电子
4	背包	服饰
6	保温杯	家居
3	运动水杯	家居

-- 查询2：有库存的下架产品（可用于清仓促销）
SELECT ProductID, ProductName, Category 
FROM Discontinued_Products
WHERE LastStock > 0
EXCEPT
SELECT ProductID, ProductName, Category 
FROM Current_Products;

查询结果：

ProductID	ProductName	Category
2	无线耳机	电子
6	保温杯	家居

8. 性能对比示例

-- 创建大型测试表
CREATE TABLE LargeTable1 (ID INT, Value VARCHAR(10));
CREATE TABLE LargeTable2 (ID INT, Value VARCHAR(10));

-- 插入10万条测试数据（实际执行时应减少数据量进行测试）
INSERT INTO LargeTable1 
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.object_id), 'Value' 
FROM sys.objects a CROSS JOIN sys.objects b;

INSERT INTO LargeTable2 
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.object_id)+50000, 'Value' 
FROM sys.objects a CROSS JOIN sys.objects b;

-- 性能测试：UNION vs UNION ALL
-- 执行以下两个查询并比较执行计划和执行时间

-- 查询1：UNION（去重）
SELECT ID FROM LargeTable1
UNION
SELECT ID FROM LargeTable2;

-- 查询2：UNION ALL（不去重）
SELECT ID FROM LargeTable1
UNION ALL
SELECT ID FROM LargeTable2;

性能结论：

• UNION ALL 比 UNION 快得多，因为它不需要去重操作
• 对于大型表，UNION 可能需要使用临时表和排序操作
• 当确定没有重复或不需要去重时，总是使用 UNION ALL

以上示例展示了 SQL Server 中集合运算的各种用法和实际应用场景，通过这些示例可以清楚地了解每种集合运算的行为和结果。

#组合视图#