詳解SQL集合運(yùn)算

本文主要是對(duì)集合運(yùn)算中并集、交集、差集運(yùn)算基礎(chǔ)的總結(jié)。

集合運(yùn)算包含四種：

1.并集運(yùn)算（兩種）

2.交集運(yùn)算

3.差集運(yùn)算

下面是集合運(yùn)算的思維導(dǎo)圖：

為什么使用集合運(yùn)算

1.在集合運(yùn)算中比聯(lián)接查詢和EXISTS/NOT EXISTS更方便。

在閱讀下面的章節(jié)時(shí)，我們可以先把環(huán)境準(zhǔn)備好，以下的SQL腳本可以幫助大家創(chuàng)建數(shù)據(jù)庫(kù)，創(chuàng)建表，插入數(shù)據(jù)。

一、集合運(yùn)算

1.集合運(yùn)算

（1）對(duì)輸入的兩個(gè)集合或多集進(jìn)行的運(yùn)算。

（2）多集：由兩個(gè)輸入的查詢生成的可能包含重復(fù)記錄的中間結(jié)果集。

（3）T-SQL支持三種集合運(yùn)算：并集（UNION）、交集（INTERSECT)、差集（EXCEPT）

2.語(yǔ)法

集合運(yùn)算的基本格式：

輸入的查詢1

集合運(yùn)算符>

輸入的查詢2

[ORDER BY]

3.要求

（1）輸入的查詢不能包含ORDER BY字句；

（2）可以為整個(gè)集合運(yùn)算結(jié)果選擇性地增加一個(gè)ORDER BY字句；

（3）每個(gè)單獨(dú)的查詢可以包含所有邏輯查詢處理階段（處理控制排列順序的ORDER BY字句）；

（4）兩個(gè)查詢 必須包含相同的列數(shù)；

（5）相應(yīng)列必須具有兼容的數(shù)據(jù)類(lèi)型。兼容個(gè)的數(shù)據(jù)類(lèi)型：優(yōu)先級(jí)較低的數(shù)據(jù)類(lèi)型必須能隱式地轉(zhuǎn)換為較高級(jí)的數(shù)據(jù)類(lèi)型。比如輸入的查詢1的第一列為int類(lèi)型，輸入的查詢2的第一列為float類(lèi)型，則較低的數(shù)據(jù)類(lèi)型int類(lèi)型可以隱式地轉(zhuǎn)換為較高級(jí)float類(lèi)型。如果輸入的查詢1的第一列為char類(lèi)型，輸入的查詢2的第一列為datetime類(lèi)型，則會(huì)提示轉(zhuǎn)換失敗：從字符串轉(zhuǎn)換日期和/或時(shí)間時(shí)，轉(zhuǎn)換失??；

（6）集合運(yùn)算結(jié)果中列名由輸入的查詢1決定，如果要為結(jié)果分配結(jié)果列，應(yīng)該在輸入的查詢1中分配相應(yīng)的別名；

（7）集合運(yùn)算時(shí)，對(duì)行進(jìn)行進(jìn)行比較時(shí)，集合運(yùn)算認(rèn)為兩個(gè)NULL相等；

（8）UNION支持DISTINCT和ALL。不能顯示指定DISTINCT字句，如果不指定ALL，則默認(rèn)使用DISTINCT；

（9）INTERSET和EXCEPT默認(rèn)使用DISTINCT，不支持ALL。

二、UNION（并集）集合運(yùn)算

1.并集的文氏圖

并集：兩個(gè)集合的并集是一個(gè)包含集合A和B中所有元素的集合。

圖中陰影區(qū)域代表集合A與集合B的并集

2.UNION ALL集合運(yùn)算

（1）假設(shè)Query1返回m行，Query2返回n行，則Query1 UNION ALL Query2返回（m+n）行；

（2）UNION ALL 不會(huì)刪除重復(fù)行，所以它的結(jié)果就是多集，而不是真正的集合；

（3）相同的行在結(jié)果中可能出現(xiàn)多次。

3.UNION DISTINCT集合運(yùn)算

（1）假設(shè)Query1返回m行，Query2返回n行，Query1和Query2有相同的h行，則Query1 UNION Query2返回（m+n-h）行；

（2）UNION 會(huì)刪除重復(fù)行，所以它的結(jié)果就是集合；

（3）相同的行在結(jié)果中只出現(xiàn)一次。

（4）不能顯示指定DISTINCT字句，如果不指定ALL，則默認(rèn)使用DISTINCT。

（5）當(dāng)Query1與Query2比較某行記錄是否相等時(shí)，會(huì)認(rèn)為取值為NULL的列是相等的列。

三、INTERSECT（交集）集合運(yùn)算

1.交集的文氏圖

交集：兩個(gè)集合（記為集合A和集合B）的交集是由既屬于A，也屬于B的所有元素組成的集合。

圖中陰影區(qū)域代表集合A與集合B的交集

2.INTERSECT DISTINCT集合運(yùn)算

（1）假設(shè)Query1返回 m 行，Query2返回 n 行，Query1和Query2有相同的 h 行，則Query1 INTERSECT Query2返回 h 行；

（2）INTERSECT集合運(yùn)算在邏輯上首先刪除兩個(gè)輸入多集中的重復(fù)行（把多集變?yōu)榧希?，然后返回只在兩個(gè)集合中都出現(xiàn)的行；

（3）INTERSECT 會(huì)刪除重復(fù)行，所以它的結(jié)果就是集合；

（4）相同的行在結(jié)果中只出現(xiàn)一次。

（5）不能顯示指定DISTINCT字句，如果不指定ALL，則默認(rèn)使用DISTINCT。

（6）當(dāng)Query1與Query2比較某行記錄是否相等時(shí)，會(huì)認(rèn)為取值為NULL的列是相等的列。

（7）用內(nèi)聯(lián)接或EXISTS謂詞可以代替INTERSECT集合運(yùn)算，但是必須對(duì)NULL進(jìn)行處理，否則這兩種方法對(duì)NULL值進(jìn)行比較時(shí)，比較結(jié)果都是UNKNOWN，這樣的行會(huì)被過(guò)濾掉。

3.INTERSECT ALL集合運(yùn)算

（1）ANSI SQL支持帶有ALL選項(xiàng)的INTERSECT集合運(yùn)算，但SQL Server2008現(xiàn)在還沒(méi)有實(shí)現(xiàn)這種運(yùn)算。后面會(huì)提供一種用于T-SQL實(shí)現(xiàn)的替代方案；

（2）假設(shè)Query1返回 m 行，Query2返回 n 行，如果行R在Query1中出現(xiàn)了x次，在Query2中出現(xiàn)了y次，則行R應(yīng)該在INTERSECT ALL運(yùn)算之后出現(xiàn)minimum(x，y)次。

下面提供用于T-SQL實(shí)現(xiàn)的INTERSECT ALL集合運(yùn)算：公用表表達(dá)式 + 排名函數(shù)

結(jié)果如下：

其中UK NULL London有四個(gè)重復(fù)行，

在排序函數(shù)的OVER字句中使用 ORDER BY ( SELECT 常量> )可以告訴SQL Server不必在意行的順序。

四、EXCEPT（差集）集合運(yùn)算

1.差集的文氏圖

差集：兩個(gè)集合（記為集合A和集合B）的由屬于集合A，但不屬于集合B的所有元素組成的集合。

圖中陰影區(qū)域代表集合A與集合B的差集

2.EXCEPT DISTINCT集合運(yùn)算

（1）假設(shè)Query1返回 m 行，Query2返回 n 行，Query1和Query2有相同的 h 行，則Query1 INTERSECT Query2返回 m – h 行,而Query2 INTERSECT Query1 返回 n – h 行

（2）EXCEPT集合運(yùn)算在邏輯上先刪除兩個(gè)輸入多集中的重復(fù)行（把多集轉(zhuǎn)變成集合），然后返回只在第一個(gè)集合中出現(xiàn)，在第二個(gè)集合眾不出現(xiàn)所有行。

（3）EXCEPT 會(huì)刪除重復(fù)行，所以它的結(jié)果就是集合；

（4）EXCEPT是不對(duì)稱的，差集的結(jié)果取決于兩個(gè)查詢的前后關(guān)系。

（5）相同的行在結(jié)果中只出現(xiàn)一次。

（6）不能顯示指定DISTINCT字句，如果不指定ALL，則默認(rèn)使用DISTINCT。

（7）當(dāng)Query1與Query2比較某行記錄是否相等時(shí)，會(huì)認(rèn)為取值為NULL的列是相等的列。

（8）用左外聯(lián)接或NOT EXISTS謂詞可以代替INTERSECT集合運(yùn)算，但是必須對(duì)NULL進(jìn)行處理，否則這兩種方法對(duì)NULL值進(jìn)行比較時(shí)，比較結(jié)果都是UNKNOWN，這樣的行會(huì)被過(guò)濾掉。

3.EXCEPT ALL集合運(yùn)算

（1）ANSI SQL支持帶有ALL選項(xiàng)的EXCEPT集合運(yùn)算，但SQL Server2008現(xiàn)在還沒(méi)有實(shí)現(xiàn)這種運(yùn)算。后面會(huì)提供一種用于T-SQL實(shí)現(xiàn)的替代方案；

（2）假設(shè)Query1返回 m 行，Query2返回 n 行，如果行R在Query1中出現(xiàn)了x次，在Query2中出現(xiàn)了y次,且x>y，則行R應(yīng)該在EXCEPT ALL運(yùn)算之后出現(xiàn) x – y 次。

下面提供用于T-SQL實(shí)現(xiàn)的EXCEPT ALL集合運(yùn)算：公用表表達(dá)式 + 排名函數(shù)

結(jié)果如下：

五、集合運(yùn)算的優(yōu)先級(jí)

1.INTERSECT>UNION=EXCEPT

2.首先計(jì)算INTERSECT,然后從左到右的出現(xiàn)順序依次處理優(yōu)先級(jí)的相同的運(yùn)算。

3.可以使用圓括號(hào)控制集合運(yùn)算的優(yōu)先級(jí)，它具有最高的優(yōu)先級(jí)。

六、特殊處理

1.只有ORDER BY能夠直接應(yīng)用于集合運(yùn)算的結(jié)果；

2.其他階段如表運(yùn)算符、WHERE、GROUP BY、HAVING等，不支持直接應(yīng)用于集合運(yùn)算的結(jié)果，這個(gè)時(shí)候可以使用表表達(dá)式來(lái)避開(kāi)這一限制。如根據(jù)包含集合運(yùn)算的查詢定義個(gè)表表達(dá)式，然后在外部查詢中對(duì)表表達(dá)式應(yīng)用任何需要的邏輯查詢處理；

3.ORDER BY字句不能直接應(yīng)用于集合運(yùn)算中的單個(gè)查詢，這個(gè)時(shí)候可以TOP+ORDER BY字句+表表達(dá)式來(lái)避開(kāi)這一限制。如定義一個(gè)基于該TOP查詢的表表達(dá)式，然后通過(guò)一個(gè)使用這個(gè)表表達(dá)式的外部查詢參與集合運(yùn)算。

七、練習(xí)題

1.寫(xiě)一個(gè)查詢，返回在2008年1月有訂單活動(dòng)，而在2008年2月沒(méi)有訂單活動(dòng)的客戶和雇員。

期望結(jié)果：

方案一：EXCEPT

（1）先用查詢1查詢出2008年1月份有訂單活動(dòng)的客戶和雇員

（2）用查詢2查詢2008年2月份客戶的訂單活動(dòng)的客戶和雇員

（3）用差集運(yùn)算符查詢2008年1月有訂單活動(dòng)而2008年2月沒(méi)有訂單活動(dòng)的客戶和雇員

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

方案二：NOT EXISTS

必須保證custid，empid不能為null，才能用NOT EXISTS進(jìn)行查詢，如果custid或empid其中有null值存在，則不能用NOT EXISTS進(jìn)行查詢，因?yàn)楸容^NULL值的結(jié)果是UNKNOWN，這樣的行用NOT EXISTS查詢返回的子查詢的行會(huì)被過(guò)濾掉，所以最后的外查詢會(huì)多出NULL值的行，最后查詢結(jié)果中會(huì)多出NULL值的行。

SELECT  custid ,
            empid
    FROM    Sales.Orders AS O1
    WHERE   orderdate >= '20080101'
            AND orderdate = '20080201'
                                    AND orderdate
如果我往Sales.Orders表中插入兩行數(shù)據(jù)：
插入cutid=NULL,empid=1,orderdate=‘20080101’
    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=‘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

用方案一查詢出來(lái)結(jié)果為50行，會(huì)把cutid=NULL,empid=1的行過(guò)濾掉

用方案二查詢出來(lái)結(jié)果為51行，不會(huì)把cutid=NULL,empid=1的行過(guò)濾掉

用下面的方案可以解決上面的問(wèn)題，需要處理cutid=NULL,或者empid=null的情況。返回50行
    SELECT  custid ,
            empid
    FROM    Sales.Orders AS O1
    WHERE   orderdate >= '20080101'
            AND orderdate = '20080201'
                                AND orderdate

2.寫(xiě)一個(gè)查詢，返回在2008年1月和在2008年2月都有訂單活動(dòng)的客戶和雇員。

期望結(jié)果：

方案一：INTERSECT

（1）先用查詢1查詢出2008年1月份有訂單活動(dòng)的客戶和雇員

（2）用查詢2查詢2008年2月份客戶的訂單活動(dòng)的客戶和雇員

（3）用交集運(yùn)算符查詢2008年1月和2008年2月都有訂單活動(dòng)的客戶和雇員

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

方案二：EXISTS

必須保證custid，empid不能為null，才能用EXISTS進(jìn)行查詢，如果custid或empid其中有null值存在，則不能用EXISTS進(jìn)行查詢，因?yàn)楸容^NULL值的結(jié)果是UNKNOWN，這樣的行用EXISTS查詢返回的子查詢的行會(huì)被過(guò)濾掉，所以最后的外查詢會(huì)少NULL值的行，最后查詢結(jié)果中會(huì)少NULL值的行。數(shù)據(jù)分析師培訓(xùn)

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

如果我往Sales.Orders表中插入兩行數(shù)據(jù)：

插入cutid=NULL,empid=1,orderdate=’20080101′

插入cutid=NULL,empid=1,orderdate=’20080201′

用方案一查詢出來(lái)結(jié)果為6行，不會(huì)把cutid=NULL,empid=1的行過(guò)濾掉

用方案二查詢出來(lái)結(jié)果為5行，會(huì)把cutid=NULL,empid=1的行過(guò)濾掉

用下面的方案可以解決上面的問(wèn)題，需要處理cutid=NULL,或者empid=null的情況。返回6行。

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

3.寫(xiě)一個(gè)查詢，返回在2008年1月和在2008年2月都有訂單活動(dòng)，而在2007年沒(méi)有訂單活動(dòng)的客戶和雇員

期望結(jié)果：

方案一：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