详解 SQL 的集合运算作者：悟空聊架构

2021 年 6 月 15 日

本文字数：8639 字

阅读完需：约 28 分钟

你好，我是 悟空。概述：

本篇主要是对集合运算中并集、交集、差集运算基础的总结。

集合运算包含三种：

1.并集运算

2.交集运算

3.差集运算

下面是集合运算的思维导图：

为什么使用集合运算：

1.在集合运算中比联接查询和 EXISTS/NOT EXISTS 更方便。

在阅读下面的章节时，我们可以先把环境准备好，以下的 SQL 脚本可以帮助大家创建数据库，创建表，插入数据。

下载脚本文件：TSQLFundamentals2008.zip一、集合运算1.集合运算

（1）对输入的两个集合或多集进行的运算。

（2）多集：由两个输入的查询生成的可能包含重复记录的中间结果集。

（3）T-SQL 支持三种集合运算：并集（UNION）、交集（INTERSECT)、差集（EXCEPT）2.语法

集合运算的基本格式：

输入的查询 1

输入的查询 2

[ORDER BY]3.要求

（1）输入的查询不能包含 ORDER BY 字句；

（2）可以为整个集合运算结果选择性地增加一个 ORDER BY 字句；

（3）每个单独的查询可以包含所有逻辑查询处理阶段（处理控制排列顺序的 ORDER BY 字句）；

（4）两个查询 必须包含相同的列数；

（5）相应列必须具有兼容的数据类型。兼容个的数据类型：优先级较低的数据类型必须能隐式地转换为较高级的数据类型。比如输入的查询 1 的第一列为 int 类型，输入的查询 2 的第一列为 float 类型，则较低的数据类型 int 类型可以隐式地转换为较高级 float 类型。如果输入的查询 1 的第一列为 char 类型，输入的查询 2 的第一列为 datetime 类型，则会提示转换失败：从字符串转换日期和/或时间时，转换失败；

（6）集合运算结果中列名由输入的查询 1 决定，如果要为结果分配结果列，应该在输入的查询 1 中分配相应的别名；

（7）集合运算时，对行进行进行比较时，集合运算认为两个 NULL 相等；

（8）UNION 支持 DISTINCT 和 ALL。不能显示指定 DISTINCT 字句，如果不指定 ALL，则默认使用 DISTINCT；

（9）INTERSET 和 EXCEPT 默认使用 DISTINCT，不支持 ALL。二、UNION（并集）集合运算1.并集的文氏图

并集：两个集合的并集是一个包含集合 A 和 B 中所有元素的集合。

图中阴影区域代表集合 A 与集合 B 的并集

2.UNION ALL 集合运算

（1）假设 Query1 返回 m 行，Query2 返回 n 行，则 Query1 UNION ALL Query2 返回（m+n）行；

（2）UNION ALL 不会删除重复行，所以它的结果就是多集，而不是真正的集合；

（3）相同的行在结果中可能出现多次。3.UNION DISTINCT 集合运算

（1）假设 Query1 返回 m 行，Query2 返回 n 行，Query1 和 Query2 有相同的 h 行，则 Query1 UNION Query2 返回（m+n-h）行；

（2）UNION 会删除重复行，所以它的结果就是集合；

（3）相同的行在结果中只出现一次。

（4）不能显示指定 DISTINCT 字句，如果不指定 ALL，则默认使用 DISTINCT。

（5）当 Query1 与 Query2 比较某行记录是否相等时，会认为取值为 NULL 的列是相等的列。三、INTERSECT（交集）集合运算1.交集的文氏图

交集：两个集合（记为集合 A 和集合 B）的交集是由既属于 A，也属于 B 的所有元素组成的集合。

图中阴影区域代表集合 A 与集合 B 的交集

2.INTERSECT DISTINCT 集合运算

（1）假设 Query1 返回 m 行，Query2 返回 n 行，Query1 和 Query2 有相同的 h 行，则 Query1 INTERSECT Query2 返回 h 行；

（2）INTERSECT 集合运算在逻辑上首先删除两个输入多集中的重复行（把多集变为集合），然后返回只在两个集合中都出现的行；

（3）INTERSECT 会删除重复行，所以它的结果就是集合；

（4）相同的行在结果中只出现一次。

（5）不能显示指定 DISTINCT 字句，如果不指定 ALL，则默认使用 DISTINCT。

（6）当 Query1 与 Query2 比较某行记录是否相等时，会认为取值为 NULL 的列是相等的列。

（7）用内联接或 EXISTS 谓词可以代替 INTERSECT 集合运算，但是必须对 NULL 进行处理，否则这两种方法对 NULL 值进行比较时，比较结果都是 UNKNOWN，这样的行会被过滤掉。3.INTERSECT ALL 集合运算

（1）ANSI SQL 支持带有 ALL 选项的 INTERSECT www.infoq.cn集合运算，但 SQL Server2008 现在还没有实现这种运算。后面会提供一种用于 T-SQL 实现的替代方案；

（2）假设 Query1 返回 m 行，Query2 返回 n 行，如果行 R 在 Query1 中出现了 x 次，在 Query2 中出现了 y 次，则行 R 应该在 INTERSECT ALL 运算之后出现 minimum(x，y)次。

下面提供用于 T-SQL 实现的 INTERSECT ALL 集合运算：公用表表达式 + 排名函数

WITH INTERSECT_ALL AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY country, region, city ORDER BY ( SELECT 0 ) ) AS rownum , country , region , city FROM HR.Employees INTERSECT SELECT ROW_NUMBER() OVER ( PARTITION BY country, region, city ORDER BY ( SELECT 0 ) ) AS rownum , country , region , city FROM Sales.Customers ) SELECT country , region , city FROM INTERSECT_ALL复制代码