写点什么

详解 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
用户头像

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

公众号:「悟空聊架构」

评论

发布
暂无评论
详解 SQL 的集合运算