详解 SQL 的集合运算

悟空聊架构

关注

发布于: 2021 年 06 月 15 日

你好，我是悟空。

概述：

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

集合运算包含三种：

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 集合运算，但 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

复制代码

结果如下：

其中 UK NULL London 有四个重复行，

在排序函数的 OVER 字句中使用 ORDER BY ( SELECT <常量> )可以告诉 SQL Server 不必在意行的顺序。

四、EXCEPT（差集）集合运算

1.差集的文氏图

差集：两个集合（记为集合 A 和集合 B）的由属于集合 A，但不属于集合 B 的所有元素组成的集合。

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

2.EXCEPT DISTINCT 集合运算

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

（2）EXCEPT 集合运算在逻辑上先删除两个输入多集中的重复行（把多集转变成集合），然后返回只在第一个集合中出现，在第二个集合众不出现所有行。

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

（4）EXCEPT 是不对称的，差集的结果取决于两个查询的前后关系。

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

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

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

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

3.EXCEPT ALL 集合运算

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

（2）假设 Query1 返回 m 行，Query2 返回 n 行，如果行 R 在 Query1 中出现了 x 次，在 Query2 中出现了 y 次,且 x>y，则行 R 应该在 EXCEPT ALL 运算之后出现 x - y 次。

下面提供用于 T-SQL 实现的 EXCEPT 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               EXCEPT               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

复制代码

结果如下：

五、集合运算的优先级

1.INTERSECT>UNION=EXCEPT

2.首先计算 INTERSECT,然后从左到右的出现顺序依次处理优先级的相同的运算。

3.可以使用圆括号控制集合运算的优先级，它具有最高的优先级。

六、特殊处理

1.只有 ORDER BY 能够直接应用于集合运算的结果；

2.其他阶段如表运算符、WHERE、GROUP BY、HAVING 等，不支持直接应用于集合运算的结果，这个时候可以使用表表达式来避开这一限制。如根据包含集合运算的查询定义个表表达式，然后在外部查询中对表表达式应用任何需要的逻辑查询处理；

3.ORDER BY 字句不能直接应用于集合运算中的单个查询，这个时候可以 TOP+ORDER BY 字句+表表达式来避开这一限制。如定义一个基于该 TOP 查询的表表达式，然后通过一个使用这个表表达式的外部查询参与集合运算。

七、练习题

1.写一个查询，返回在 2008 年 1 月有订单活动，而在 2008 年 2 月没有订单活动的客户和雇员。

期望结果：

方案一：EXCEPT

（1）先用查询 1 查询出 2008 年 1 月份有订单活动的客户和雇员

（2）用查询 2 查询 2008 年 2 月份客户的订单活动的客户和雇员

（3）用差集运算符查询 2008 年 1 月有订单活动而 2008 年 2 月没有订单活动的客户和雇员

SELECT  custid ,        empidFROM    Sales.OrdersWHERE   orderdate >= '20080101'        AND orderdate < '20080201'EXCEPTSELECT  custid ,        empidFROM    Sales.OrdersWHERE   orderdate >= '20080201'        AND orderdate < '20080301'``` sql
**方案二：NOT EXISTS**
必须保证custid，empid不能为null，才能用NOT EXISTS进行查询，如果custid或empid其中有null值存在，则不能用NOT EXISTS进行查询，因为比较NULL值的结果是UNKNOWN，这样的行用NOT EXISTS查询返回的子查询的行会被过滤掉，所以最后的外查询会多出NULL值的行，最后查询结果中会多出NULL值的行。

复制代码

SELECT custid ,empidFROM Sales.Orders AS O1WHERE orderdate >= '20080101'AND orderdate < '20080201'AND NOT EXISTS ( SELECT *FROM Sales.OrdersWHERE orderdate >= '20080201'AND orderdate < '20080301'AND custid = o1.custidAND empid = o1.empid )ORDER BY O1.custid ,

    O1.empid

复制代码


如果我往Sales.Orders表中插入两行数据：
插入cutid=NULL,empid=1,orderdate='20080101'``` sqlINSERT  INTO [TSQLFundamentals2008].[Sales].[Orders]        ( [custid] ,          [empid] ,          [orderdate] ,          [requireddate] ,          [shippeddate] ,          [shipperid] ,          [freight] ,          [shipname] ,          [shipaddress] ,          [shipcity] ,          [shipregion] ,          [shippostalcode] ,          [shipcountry]        )VALUES  ( NULL ,          1 ,          '20080101' ,          '20080101' ,          '20080101' ,          1 ,          1 ,          'A' ,          '20080101' ,          'A' ,          'A' ,          'A' ,          'A'        )GO

复制代码

插入 cutid=NULL,empid=1,orderdate='20080201'

INSERT  INTO [TSQLFundamentals2008].[Sales].[Orders]        ( [custid] ,          [empid] ,          [orderdate] ,          [requireddate] ,          [shippeddate] ,          [shipperid] ,          [freight] ,          [shipname] ,          [shipaddress] ,          [shipcity] ,          [shipregion] ,          [shippostalcode] ,          [shipcountry]        )VALUES  ( NULL ,          1 ,          '20080201' ,          '20080101' ,          '20080101' ,          1 ,          1 ,          'A' ,          '20080101' ,          'A' ,          'A' ,          'A' ,          'A'        )GO

复制代码

用方案一查询出来结果为 50 行，会把 cutid=NULL,empid=1 的行过滤掉

用方案二查询出来结果为 51 行，不会把 cutid=NULL,empid=1 的行过滤掉

用下面的方案可以解决上面的问题，需要处理 cutid=NULL,或者 empid=null 的情况。返回 50 行

SELECT  custid ,        empidFROM    Sales.Orders AS O1WHERE   orderdate >= '20080101'        AND orderdate < '20080201'        AND NOT EXISTS ( SELECT *                     FROM   Sales.Orders                     WHERE  orderdate >= '20080201'                            AND orderdate < '20080301'                            AND ( custid = o1.custid                                  AND empid = o1.empid                                )                            OR ( o1.custid IS NULL                                 AND custid IS NULL                                 AND empid = o1.empid                               )                            OR ( o1.custid IS NULL                                 AND custid IS NULL                                 AND o1.empid IS NULL                                 AND custid IS NULL                               )                            OR ( custid = o1.custid                                 AND o1.empid IS NULL                                 AND custid IS NULL                               ) )

复制代码

2.写一个查询，返回在 2008 年 1 月和在 2008 年 2 月都有订单活动的客户和雇员。

期望结果：

方案一：INTERSECT

（1）先用查询 1 查询出 2008 年 1 月份有订单活动的客户和雇员

（2）用查询 2 查询 2008 年 2 月份客户的订单活动的客户和雇员

（3）用交集运算符查询 2008 年 1 月和 2008 年 2 月都有订单活动的客户和雇员

SELECT  custid ,        empidFROM    Sales.OrdersWHERE   orderdate >= '20080101'        AND orderdate < '20080201'INTERSECTSELECT  custid ,        empidFROM    Sales.OrdersWHERE   orderdate >= '20080201'        AND orderdate < '20080301'

复制代码

方案二：EXISTS

必须保证 custid，empid 不能为 null，才能用 EXISTS 进行查询，如果 custid 或 empid 其中有 null 值存在，则不能用 EXISTS 进行查询，因为比较 NULL 值的结果是 UNKNOWN，这样的行用 EXISTS 查询返回的子查询的行会被过滤掉，所以最后的外查询会少 NULL 值的行，最后查询结果中会少 NULL 值的行。

SELECT  custid ,        empidFROM    Sales.Orders AS O1WHERE   orderdate >= '20080101'        AND orderdate < '20080201'        AND EXISTS ( SELECT *                     FROM   Sales.Orders                     WHERE  orderdate >= '20080201'                            AND orderdate < '20080301'                            AND custid = o1.custid                            AND empid = o1.empid )ORDER BY O1.custid ,        O1.empid

复制代码

如果我往 Sales.Orders 表中插入两行数据：

插入 cutid=NULL,empid=1,orderdate='20080101'

插入 cutid=NULL,empid=1,orderdate='20080201'

用方案一查询出来结果为 6 行，不会把 cutid=NULL,empid=1 的行过滤掉

用方案二查询出来结果为 5 行，会把 cutid=NULL,empid=1 的行过滤掉

用下面的方案可以解决上面的问题，需要处理 cutid=NULL,或者 empid=null 的情况。返回 6 行。

SELECT  custid ,        empidFROM    Sales.Orders AS O1WHERE   orderdate >= '20080101'        AND orderdate < '20080201'        AND EXISTS ( SELECT *                     FROM   Sales.Orders                     WHERE  orderdate >= '20080201'                            AND orderdate < '20080301'                            AND ( custid = o1.custid                                  AND empid = o1.empid                                )                            OR ( o1.custid IS NULL                                 AND custid IS NULL                                 AND empid = o1.empid                               )                            OR ( o1.custid IS NULL                                 AND custid IS NULL                                 AND o1.empid IS NULL                                 AND custid IS NULL                               )                            OR ( custid = o1.custid                                 AND o1.empid IS NULL                                 AND custid IS NULL                               ) )

复制代码

3.写一个查询，返回在 2008 年 1 月和在 2008 年 2 月都有订单活动，而在 2007 年没有订单活动的客户和雇员

期望结果：

方案一：INTERSECT + EXCEPT

SELECT  custid ,        empidFROM    Sales.OrdersWHERE   orderdate >= '20080101'        AND orderdate < '20080201'INTERSECTSELECT  custid ,        empidFROM    Sales.OrdersWHERE   orderdate >= '20080201'        AND orderdate < '20080301'EXCEPTSELECT  custid ,        empidFROM    Sales.OrdersWHERE   orderdate >= '20070101'        AND orderdate < '20080101'

复制代码

方案二：EXISTS + NOT EXISTS

SELECT  custid ,        empidFROM    Sales.Orders AS O1WHERE   orderdate >= '20080101'        AND orderdate < '20080201'        AND EXISTS ( SELECT *                     FROM   Sales.Orders                     WHERE  orderdate >= '20080201'                            AND orderdate < '20080301'                            AND ( custid = o1.custid                                  AND empid = o1.empid                                )                            OR ( custid = o1.custid                                 AND custid IS NULL                                 AND empid = o1.empid                               )                            OR ( custid = o1.custid                                 AND custid IS NULL                                 AND empid = o1.empid                                 AND custid IS NULL                               )                            OR ( custid = o1.custid                                 AND empid = o1.empid                                 AND custid IS NULL                               )                            OR ( custid = o1.custid                                 AND custid IS NULL                                 AND empid = o1.empid                                 AND custid IS NULL                               ) )        AND NOT EXISTS ( SELECT *                         FROM   Sales.Orders                         WHERE  orderdate >= '20070101'                                AND orderdate < '20080101'                                AND ( custid = o1.custid                                      AND empid = o1.empid                                    )                                AND ( custid = o1.custid                                      AND empid = o1.empid                                    )                                OR ( custid = o1.custid                                     AND custid IS NULL                                     AND empid = o1.empid                                   )                                OR ( custid = o1.custid                                     AND custid IS NULL                                     AND empid = o1.empid                                     AND custid IS NULL                                   )                                OR ( custid = o1.custid                                     AND empid = o1.empid                                     AND custid IS NULL                                   )                                OR ( custid = o1.custid                                     AND custid IS NULL                                     AND empid = o1.empid                                     AND custid IS NULL                                   ) )ORDER BY O1.custid ,        O1.empid

复制代码

参考资料：

《SQL2008 技术内幕：T-SQL 语言基础》

欢迎关注我的公众号：「悟空聊架构」

作者简介：8 年互联网职场老兵｜全栈工程师｜90 后超级奶爸｜开源践行者｜公众号万粉原创号主。蓝桥签约作者，著有《JVM 性能调优实战》专栏，手写了一套 7 万字 SpringCloud 实战总结和 3 万字分布式算法总结。欢迎关注我的公众号「悟空聊架构」，免费获取资料学习。

我是悟空，努力变强，变身超级赛亚人！

发布于: 2021 年 06 月 15 日阅读数: 556

原文链接:【http://xie.infoq.cn/article/38903e35a0e69d5cb7f210498】。未经作者许可，禁止转载。

悟空聊架构

关注

用故事、大白话讲解Java、分布式、架构设计 2018.05.06 加入

公众号：「悟空聊架构」

发布

暂无评论

创作场景

详解 SQL 的集合运算

概述：

为什么使用集合运算：

一、集合运算

1.集合运算

2.语法

3.要求

二、UNION（并集）集合运算

1.并集的文氏图

2.UNION ALL 集合运算

3.UNION DISTINCT 集合运算

三、INTERSECT（交集）集合运算

1.交集的文氏图

2.INTERSECT DISTINCT 集合运算

3.INTERSECT ALL 集合运算

四、EXCEPT（差集）集合运算

1.差集的文氏图

2.EXCEPT DISTINCT 集合运算

3.EXCEPT ALL 集合运算

五、集合运算的优先级

六、特殊处理

七、练习题

1.写一个查询，返回在 2008 年 1 月有订单活动，而在 2008 年 2 月没有订单活动的客户和雇员。

2.写一个查询，返回在 2008 年 1 月和在 2008 年 2 月都有订单活动的客户和雇员。

3.写一个查询，返回在 2008 年 1 月和在 2008 年 2 月都有订单活动，而在 2007 年没有订单活动的客户和雇员

悟空聊架构

评论