第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操作的基本语法如下：

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

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

7.2.2 示例

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

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操作用于找出两个或多个查询结果集的共同部分，其基本语法如下：

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

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

7.3.2 示例

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

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 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 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查询能力、优化数据分析和报表生成过程具有重要意义。在实际应用中，应根据具体的数据结构和查询需求，灵活选择和使用这些集合运算，以达到最佳的数据处理效果。