以前总是追求新东西，发现基础最重要。今年的主要目标是精通SQL查询和SQL性能优化。概述本文主要是对集合运算中并、交、差运算的基础知识进行总结。集合运算有四种：1.并运算（两种）2.交运算3.差运算以下是集合运算的思维导图：为什么要用集合运算1.与joinquery和EXISTS/NOTinset比较操作EXISTS比较方便。在阅读后面的章节时，我们可以先准备好环境。以下SQL脚本可以帮助您创建数据库、创建表和插入数据。1.Setoperation1.Setoperation(1)对两组或多组输入进行的运算。(2)Multiset：中间结果集，可能包含由两个输入查询产生的重复记录。（3）T-SQL支持三种集合运算：并（UNION）、交（INTERSECT）、差（EXCEPT）2、语法集合运算的基本格式：输入查询1集合运算符>输入查询2[ORDERBY]3、要求（1）输入查询不能包含ORDERBY子句；(2)可以选择性的在整个set操作结果中加入一个ORDERBY子句；(3)每个单独的查询可以包含所有逻辑查询处理阶段（处理控制排序顺序的ORDERBY子句）；(4)两个查询必须包含相同数量的列；(5)对应的列必须有兼容的数据类型。兼容的数据类型：优先级较低的数据类型必须可以隐式转换为较高的数据类型。例如输入查询1的***列是int类型，输入查询2的***列是float类型，那么可以将低层数据类型int类型隐式转换为高层float类型.如果输入查询1的***列是char类型，输入查询2的***列是datetime类型，会提示转换失败：Whenconvertingdateand/ortimefroma字符串，转换失败；(6)集合操作结果中的列名由输入查询1决定，如果要将结果列赋值给结果，应在输入查询1中赋值对应的别名；(7)执行集合操作时，在比较行时，集合操作认为两个NULL相等；(8)UNION支持DISTINCT和ALL。无法显示指定的DISTINCT语句。如果不指定ALL，则默认使用DISTINCT；(9)INTERSET和EXCEPT默认使用DISTINCT，不支持ALL。二、UNION（并集）集合运算1、并集的维恩图并集：两个集合的并集是一个包含集合A和B中所有元素的集合。图中阴影区域表示集合A和集合B的并集。2.UNIONALL集合操作(1)假设Query1返回m行，Query2返回n行，则Query1UNIONALLQuery2返回(m+n)行；(2)UNIONALL不去除重复行，所以它的结果是一个multiset，不是一个真集；(3)同一行可能在结果中出现多次。3、UNIONDISTINCT集合操作(1)假设Query1返回m行，Query2返回n行，Query1和Query2有相同的h行，则Query1UNIONQuery2返回(m+n-h)行；(2)UNION会删除重复的行，所以它的结果就是集合；(3)同一行在结果中只出现一次。(4)无法显示指定的DISTINCT语句。如果未指定ALL，则默认使用DISTINCT。(5)当Query1和Query2比较某行记录是否相等时，会认为值为NULL的列为相等列。三、INTERSECT（交集）集合运算1、维恩图交集交集：两个集合（记为集合A和集合B）的交集是由同时属于A和B的所有元素组成的集合。图中阴影部分表示集合A和集合B的交集。2.INTERSECTDISTINCT集合操作(1)假设Query1返回m行，Query2返回n行，Query1和Query2有相同的h行，则Query1INTERSECTQuery2返回h行；(2)INTERSECT集合操作逻辑上先删除两个输入的multi-set中重复的行（将multi-set变成一个集合），然后返回只出现在两个集合中的行；(3)INTERSECT会删除重复的行，因此Itsresultistheset；(4)同一行在结果中只出现一次。(5)无法显示指定的DISTINCT语句。如果未指定ALL，则默认使用DISTINCT。(6)当Query1和Query2比较一行记录是否相等时，会认为值为NULL的列为相等列。(7)可以使用内连接或EXISTS谓词代替INTERSECT集合操作，但必须处理NULL，否则这两种方法比较NULL值时，比较结果为UNKNOWN，此类行将被过滤掉。3.INTERSECTALL集合操作(1)ANSISQL支持INTERSECT集合操作带ALL选项，但是SQLServer2008还没有实现这个操作。稍后将提供T-SQL实现的替代解决方案；(2)假设Query1返回m行，Query2返回n行，如果R行在Query1中出现x次，在Query2中出现y次，则R行在INTERSECTALL运算后出现minimum(x,y)次。下面给出T-SQL实现的INTERSECTALL集合操作：普通表表达式+排序函数结果如下：UKNULLLondon有四个重复行，ORDERBY(SELECT常量>)可以用在OVER子句中排序函数告诉SQLServer不要关心行的顺序。4.EXCEPT（差分集）集合运算1.差分集维恩图差分集：两个集合（记为集合A和集合B）由属于集合A但不属于集合B的所有元素组成。图中的阴影区域图中表示集合A和集合B的区别。2.EXCEPTDISTINCT集合操作(1)假设Query1返回m行，Query2返回n行，Query1和Query2有相同的h行，则Query1INTERSECTQuery2返回m–hrow,whileQuery2INTERSECTQuery1returnsn–hrows(2)EXCEPTset操作逻辑删除两个输入multi-set中的重复行（将multi-set转换为set），然后只返回出现在第一个set中的所有行没有出现在第二盘。(3)EXCEPT会删除重复的行，所以它的结果是一个集合；(4)EXCEPT是非对称的，差异的结果取决于两次查询的上下文。(5)同一行在结果中只出现一次。(6)无法显示指定的DISTINCT语句。如果未指定ALL，则默认使用DISTINCT。(7)当Query1和Query2比较某行记录是否相等时，会认为值为NULL的列为相等列。(8)可以使用左外连接或NOTEXISTS谓词代替INTERSECT集合操作，但必须处理NULL，否则这两种方法比较NULL值时，比较结果为UNKNOWN，这样的行将被过滤掉。3.EXCEPTALL集合操作(1)ANSISQL支持带ALL选项的EXCEPT集合操作，但是SQLServer2008还没有实现这个操作。稍后将提供T-SQL实现的替代解决方案；(2)假设Query1返回m行，Query2返回n行，如果行R在Query1中出现x次，在Query2中出现y次，且x>y，则行R在EXCEPTALL操作后应出现x-y次。T-SQL实现的EXCEPTALL集合操作如下：公用表表达式+排序函数WITHINTERSECT_ALLAS( SELECTROW_NUMBER()OVER(PARTITIONBYcountry,region,cityORDERBY(SELECT0))ASrownum,country,region,cityFROMHR.EmployeesEXCEPTSELECTROW_NUMBER()OVER(PARTITIONBYcountry,region,cityORDERBY(SELECT0))ASrownum,country,region,cityFROMSales.Customers)SELECTcountry,region,cityFROMINTERSECT_ALL结果如下：5、集合操作的优先级1.INTERSECT>UNION=EXCEPT2。先计算INTERSECT，然后相同优先级的Operations按照出现的顺序从左到右依次处理。3、括号可以用来控制set操作的优先级，哪个优先级最高。六、特殊处理1、集合运算的结果只能直接应用ORDERBY；2.其他阶段如表操作符，WHERE，GROUPBY，HAVING等，不支持将结果直接应用到集合操作，此时可以使用表表达式来绕过这个限制。例如，基于包含集合操作的查询定义一个表表达式，然后在外部查询中对表表达式应??用任何需要的逻辑查询处理；3.ORDERBY子句不能直接应用于集合操作中的单个查询。这时候可以通过TOP+ORDERBY子句+表表达式来绕过这个限制。比如定义一个基于TOP查询的表表达式，然后使用这个表表达式通过外部查询参与集合运算。7.练习1.编写一个查询，返回在2008年1月有订单活动但在2008年2月没有订单活动的客户和员工。预期结果：Option1:EXCEPT(1)先用查询1查询2008年1月有订单的客户和员工(2)用查询2查询2008年2月有订单的客户和员工(3)用差setoperator查询2008年1月有订单活动但2008年2月没有订单活动的客户和员工SELECTcustid,empidFROMSales.OrdersWHEREorderdate>='20080101'ANDorderdate='20080201'ANDorderdate方案2:NOTEXISTS必须保证custid,empid不能是null，所以可以使用NOTEXISTS来查询。如果custid或empid中存在空值，则不能使用NOTEXISTS查询，因为NULL值比较的结果是UNKNOWN。这样的行在NOTEXISTS查询返回的子查询返回的行中会被过滤掉，所以***的外层查询会有更多的NULL值行，而***查询的结果也会有更多的NULL值行。SELECTcustid,empidFROMSales.OrdersASO1WHEREorderdate>='20080101'ANDorderdate='20080201'ANDorderdate如果我将两行数据插入Sales.Orders表：insertcutid=NULL,empid=1,orderdate='20080101'INSERTINTO[TSQLFundamentals.[2008].].[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')GOINSERTcutid=NULL,empid=1,orderdate='20080201'INSERTINTO[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使用plan1查询结果为50行，cutid=NULL,empid=1使用方案二查??询结果为51行，cutid=NULL，empid=1的行不会被过滤掉。上面的问题可以用下面的方案解决，需要处理cutid=NULL或者empid=null的情况。.返回50行SELECTcustid,empidFROMSales.OrdersASO1WHEREorderdate>='20080101'ANDorderdate='20080201'ANDorderdate2。写一个查询，返回2008年1月和2008年2月有订单活动的客户和员工。预期结果：方案一：INTERSECT（1）首先使用查询1查询2008年1月有订单活动的客户和员工（2）使用查询2查询2008年2月有客户订单活动的客户和员工（3）使用交集运算符查询2008年1月和2008年2月有订单活动的客户和员工SELECTcustid,empidFROMSales.OrdersWHEREorderdate>='20080101'ANDorderdate='20080201'ANDorderdateScheme2：EXISTS必须保证custid，empid不能为null，只能用EXISTS查询，如果custid或empid中有null值，则不能用EXISTS查询，因为NULL值比较的结果是UNKNOWN，这样行会被EXISTS查询返回的子查询行过滤掉，所以***的外层查询会有较少的NULL值行，***查询结果行也会有较少的NULL值行。SELECTcustid,empidFROMSales.OrdersASO1WHEREorderdate>='20080101'ANDorderdate='20080201'ANDorderdate如果我在Sales.Orders表中插入两行数据：insertcutid=NULL,empid=1,orderdate='20080101',insertcutid=NULL,empid=1,orderdate='20080201'，scheme1查询结果为6行，cutid=NULL，empid=1的行不会被过滤掉。方案2的查询结果是5行，cutid=NULL,empid=1的查询结果上面的问题可以通过下面的解决方案过滤掉行，cutid=NULL或者empid的情况解决=null需要处理。返回6行。SELECTcustid，empidFROMSales.OrdersASO1WHEREorderdate>='20080101'ANDorderdate='20080201'ANDorderdate3。编写查询以返回在2008年1月和2008年2月有订单活动但在2007年没有订单活动的客户和员工结果：方法一：INTERSECT+EXCEPTSELECTcustid,empidFROMSales.OrdersWHEREorderdate>='20080101'ANDorderdate='20080201'ANDorderdate='20070101'ANDorderdate方法二：EXISTS+NOTEXISTSSELECTcustid,empidFROMSales.OrdersASO1WHEREorderdate>='20080101'ANDorderdate='20080201'ANDorderdate='20070101'ANDorderdate
