如何理解SQL语句的集合运算

共计 6656 个字符，预计需要花费 17 分钟才能阅读完成。

今天就跟大家聊聊有关如何理解 SQL 语句的集合运算，可能很多人都不太了解，为了让大家更加了解，丸趣 TV 小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

以前总是追求新东西，发现基础才是最重要的，今年主要的目标是精通 SQL 查询和 SQL 性能优化。

概述

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

集合运算包含四种：

1. 并集运算(两种)

2. 交集运算

3. 差集运算

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

为什么使用集合运算

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

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

一、集合运算

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 集合运算：公用表表达式 + 排名函数

结果如下：

其中 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 ndash; h 行, 而 Query2 INTERSECT Query1 返回 n ndash; 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 ndash; 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 , empid FROM Sales.Orders WHERE orderdate  =  20080101  AND orderdate =  20080201  AND orderdate

方案二：NOT EXISTS

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

SELECT custid , empid FROM Sales.Orders AS O1 WHERE orderdate  =  20080101  AND orderdate =  20080201  AND orderdate

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

插入 cutid=NULL,empid=1,orderdate= lsquo;20080101 rsquo;

INSERT 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= lsquo;20080201 rsquo;

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 , empid FROM Sales.Orders AS O1 WHERE orderdate  =  20080101  AND orderdate =  20080201  AND orderdate

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

期望结果：

方案一：INTERSECT

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

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

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

SELECT custid , empid FROM Sales.Orders WHERE orderdate  =  20080101  AND orderdate =  20080201  AND orderdate

方案二：EXISTS

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

SELECT custid , empid FROM Sales.Orders AS O1 WHERE orderdate  =  20080101  AND orderdate =  20080201  AND orderdate

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

插入 cutid=NULL,empid=1,orderdate= rsquo;20080101 prime;

插入 cutid=NULL,empid=1,orderdate= rsquo;20080201 prime;

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

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

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

SELECT custid , empid FROM Sales.Orders AS O1 WHERE orderdate  =  20080101  AND orderdate =  20080201  AND orderdate

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

期望结果：

方案一：INTERSECT + EXCEPT

SELECT custid , empid FROM Sales.Orders WHERE orderdate  =  20080101  AND orderdate =  20080201  AND orderdate =  20070101  AND orderdate

方案二：EXISTS + NOT EXISTS

SELECT custid , empid FROM Sales.Orders AS O1 WHERE orderdate  =  20080101  AND orderdate =  20080201  AND orderdate =  20070101  AND orderdate

看完上述内容，你们对如何理解 SQL 语句的集合运算有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注丸趣 TV 行业资讯频道，感谢大家的支持。