第7章　集合运算-SQL基础教程(中)

当前位置:　首页>> 技术小册>> SQL基础教程(中)

### 第7章 集合运算

在SQL（Structured Query Language）的世界中，集合运算是处理数据集合时不可或缺的一部分，它允许我们通过特定的逻辑规则合并、比较或筛选多个查询结果集。集合运算主要包括UNION、INTERSECT和EXCEPT（或在某些数据库系统中称为MINUS）三种基本类型。这些操作对于数据清洗、报告生成以及复杂的数据分析任务至关重要。本章将深入探讨这三种集合运算的原理、用法、注意事项及实际应用场景。

#### 7.1 集合运算基础

首先，我们需要理解集合运算的基本概念。在SQL中，每个查询结果都可以被视为一个集合，集合中的元素是唯一的（尽管在某些情况下，如未使用DISTINCT关键字，可能会包含重复项）。集合运算就是对这些集合进行操作，以产生新的集合结果。

- **UNION**：用于合并两个或多个SELECT语句的结果集，并自动去除重复行。默认情况下，UNION会去除重复行，如果希望保留重复行，则使用UNION ALL。
- **INTERSECT**：返回两个或多个SELECT语句结果集的交集，即同时存在于所有结果集中的行。注意，并非所有数据库系统都支持INTERSECT操作。
- **EXCEPT/MINUS**：返回存在于第一个SELECT语句结果集中但不在后续SELECT语句结果集中的行。其名称取决于使用的数据库系统，如SQL Server使用EXCEPT，而Oracle则使用MINUS。

#### 7.2 UNION运算

##### 7.2.1 基本用法

UNION操作的基本语法如下：

```sql
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
```

这里，两个SELECT语句必须选择相同数量的列，并且这些列的数据类型也需要兼容，以便能够合并成一个统一的集合。

##### 7.2.2 示例

假设我们有两个表，`employees_usa`（美国员工）和`employees_europe`（欧洲员工），都包含员工的ID和姓名。我们可以使用UNION来合并这两个表中的员工信息：

```sql
SELECT employee_id, name FROM employees_usa
UNION
SELECT employee_id, name FROM employees_europe;
```

这将返回一个包含所有独特员工ID和姓名的列表，不区分他们来自哪个地区。

##### 7.2.3 注意事项

- 默认情况下，UNION会去除重复行。如果希望保留所有行（包括重复行），应使用UNION ALL。
- UNION操作的列顺序必须相同，且数据类型兼容。
- UNION不仅限于两个查询，可以连接多个查询结果。

#### 7.3 INTERSECT运算

##### 7.3.1 基本用法

INTERSECT操作用于找出两个或多个查询结果集的共同部分，其基本语法如下：

```sql
SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;
```

同样，参与INTERSECT的SELECT语句必须选择相同数量的列，并且列的数据类型也需要兼容。

##### 7.3.2 示例

假设我们有两个销售数据表，`sales_2022`和`sales_2023`，都记录了产品的ID和销售额。我们想要找出这两年都有销售记录的产品：

```sql
SELECT product_id FROM sales_2022
INTERSECT
SELECT product_id FROM sales_2023;
```

这将返回同时出现在`sales_2022`和`sales_2023`表中的`product_id`。

##### 7.3.3 注意事项

- 并非所有数据库系统都支持INTERSECT操作，遇到不支持的情况时，可能需要使用其他方法（如JOIN操作）来模拟INTERSECT的行为。
- INTERSECT同样会去除结果集中的重复行。

#### 7.4 EXCEPT/MINUS运算

##### 7.4.1 基本用法

EXCEPT（在SQL Server等系统中）或MINUS（在Oracle等系统中）用于返回存在于第一个查询结果中但不在后续查询结果中的行。其基本语法如下：

```sql
-- SQL Server
SELECT column_name(s) FROM table1
EXCEPT
SELECT column_name(s) FROM table2;

-- Oracle
SELECT column_name(s) FROM table1
MINUS
SELECT column_name(s) FROM table2;
```

##### 7.4.2 示例

假设我们想要找出在`employees_usa`表中但不在`employees_europe`表中的员工：

```sql
-- SQL Server
SELECT employee_id, name FROM employees_usa
EXCEPT
SELECT employee_id, name FROM employees_europe;

-- Oracle
SELECT employee_id, name FROM employees_usa
MINUS
SELECT employee_id, name FROM employees_europe;
```

这将返回仅在`employees_usa`表中存在的员工信息。

##### 7.4.3 注意事项

- 与UNION和INTERSECT一样，参与EXCEPT/MINUS的SELECT语句必须选择相同数量的列，并且列的数据类型也需要兼容。
- EXCEPT/MINUS同样会去除结果集中的重复行。
- 考虑到兼容性问题，如果需要在不同数据库系统间迁移代码，需要特别注意这些系统对集合运算的支持情况。

#### 7.5 集合运算的高级应用

集合运算不仅可以单独使用，还可以与其他SQL功能（如子查询、窗口函数等）结合，以实现更复杂的数据分析需求。例如，可以利用集合运算来比较不同时间点的数据变化，或者筛选出特定条件下的唯一记录集。

此外，在处理大数据集时，合理使用集合运算可以显著提高查询效率，但也需要注意其对数据库性能的影响，特别是当处理大量数据时，应谨慎使用INTERSECT和EXCEPT/MINUS，因为它们可能需要更多的计算资源来去除重复行。

#### 7.6 小结

集合运算是SQL中强大的工具，它们允许我们以直观的方式合并、比较和筛选数据集合。掌握UNION、INTERSECT和EXCEPT/MINUS的基本用法和高级技巧，对于提升SQL查询能力、优化数据分析和报表生成过程具有重要意义。在实际应用中，应根据具体的数据结构和查询需求，灵活选择和使用这些集合运算，以达到最佳的数据处理效果。

该分类下的相关小册推荐：

PostgreSQL入门教程

SQL基础教程(上)

SQL基础教程(下)

高性能的Postgres SQL