集合運算包含四種：
1.并集運算
2.交集運算
3.差集運算

為什么使用集合運算：
1.在集合運算中比聯(lián)接查詢和EXISTS/NOT EXISTS更方便掖举。
在閱讀下面的章節(jié)時肢簿，我們可以先把環(huán)境準(zhǔn)備好妙真，以下的SQL腳本可以幫助大家創(chuàng)建數(shù)據(jù)庫和措，創(chuàng)建表应又，插入數(shù)據(jù)问畅。
一艾栋、集合運算
1.集合運算
（1）對輸入的兩個集合或多集進(jìn)行的運算爆存。
（2）多集：由兩個輸入的查詢生成的可能包含重復(fù)記錄的中間結(jié)果集。
（3）T-SQL支持三種集合運算：并集（UNION）蝗砾、交集（INTERSECT)先较、差集（EXCEPT）
2.語法
集合運算的基本格式：
輸入的查詢1
集合運算符>
輸入的查詢2
[ORDER BY]
3.要求
（1）輸入的查詢不能包含ORDER BY字句携冤；
（2）可以為整個集合運算結(jié)果選擇性地增加一個ORDER BY字句；
（3）每個單獨的查詢可以包含所有邏輯查詢處理階段（處理控制排列順序的ORDER BY字句）闲勺；
（4）兩個查詢 必須包含相同的列數(shù)曾棕；
（5）相應(yīng)列必須具有兼容的數(shù)據(jù)類型。兼容個的數(shù)據(jù)類型：優(yōu)先級較低的數(shù)據(jù)類型必須能隱式地轉(zhuǎn)換為較高級的數(shù)據(jù)類型菜循。比如輸入的查詢1的第一列為int類型翘地，輸入的查詢2的第一列為float類型，則較低的數(shù)據(jù)類型int類型可以隱式地轉(zhuǎn)換為較高級float類型癌幕。如果輸入的查詢1的第一列為char類型衙耕，輸入的查詢2的第一列為datetime類型，則會提示轉(zhuǎn)換失斏自丁：從字符串轉(zhuǎn)換日期和/或時間時橙喘，轉(zhuǎn)換失敗谚中；
（6）集合運算結(jié)果中列名由輸入的查詢1決定渴杆，如果要為結(jié)果分配結(jié)果列，應(yīng)該在輸入的查詢1中分配相應(yīng)的別名宪塔；
（7）集合運算時磁奖，對行進(jìn)行進(jìn)行比較時，集合運算認(rèn)為兩個NULL相等某筐；
（8）UNION支持DISTINCT和ALL比搭。不能顯示指定DISTINCT字句，如果不指定ALL南誊，則默認(rèn)使用DISTINCT身诺；
（9）INTERSET和EXCEPT默認(rèn)使用DISTINCT，不支持ALL抄囚。
二霉赡、UNION（并集）集合運算
1.并集的文氏圖
并集：兩個集合的并集是一個包含集合A和B中所有元素的集合。
圖中陰影區(qū)域代表集合A與集合B的并集

2.UNION ALL集合運算
（1）假設(shè)Query1返回m行幔托，Query2返回n行穴亏，則Query1 UNION ALL Query2返回（m+n）行；
（2）UNION ALL 不會刪除重復(fù)行重挑，所以它的結(jié)果就是多集嗓化，而不是真正的集合；
（3）相同的行在結(jié)果中可能出現(xiàn)多次谬哀。
3.UNION DISTINCT集合運算
（1）假設(shè)Query1返回m行刺覆，Query2返回n行，Query1和Query2有相同的h行史煎，則Query1 UNION Query2返回（m+n-h）行谦屑；
（2）UNION 會刪除重復(fù)行驳糯，所以它的結(jié)果就是集合；
（3）相同的行在結(jié)果中只出現(xiàn)一次伦仍。
（4）不能顯示指定DISTINCT字句结窘，如果不指定ALL，則默認(rèn)使用DISTINCT充蓝。
（5）當(dāng)Query1與Query2比較某行記錄是否相等時隧枫，會認(rèn)為取值為NULL的列是相等的列。
三谓苟、INTERSECT（交集）集合運算
1.交集的文氏圖
交集：兩個集合（記為集合A和集合B）的交集是由既屬于A官脓，也屬于B的所有元素組成的集合。
圖中陰影區(qū)域代表集合A與集合B的交集

2.INTERSECT DISTINCT集合運算
（1）假設(shè)Query1返回 m 行涝焙，Query2返回 n 行卑笨，Query1和Query2有相同的 h 行，則Query1 INTERSECT Query2返回 h 行仑撞；
（2）INTERSECT集合運算在邏輯上首先刪除兩個輸入多集中的重復(fù)行（把多集變?yōu)榧希┏嘈耍缓蠓祷刂辉趦蓚€集合中都出現(xiàn)的行；
（3）INTERSECT 會刪除重復(fù)行隧哮，所以它的結(jié)果就是集合桶良；
（4）相同的行在結(jié)果中只出現(xiàn)一次。
（5）不能顯示指定DISTINCT字句沮翔，如果不指定ALL陨帆，則默認(rèn)使用DISTINCT。
（6）當(dāng)Query1與Query2比較某行記錄是否相等時采蚀，會認(rèn)為取值為NULL的列是相等的列疲牵。
（7）用內(nèi)聯(lián)接或EXISTS謂詞可以代替INTERSECT集合運算，但是必須對NULL進(jìn)行處理榆鼠，否則這兩種方法對NULL值進(jìn)行比較時纲爸，比較結(jié)果都是UNKNOWN，這樣的行會被過濾掉妆够。
3.INTERSECT ALL集合運算
（1）ANSI SQL支持帶有ALL選項的INTERSECT集合運算识啦，但SQL Server2008現(xiàn)在還沒有實現(xiàn)這種運算。后面會提供一種用于T-SQL實現(xiàn)的替代方案责静；
（2）假設(shè)Query1返回 m 行袁滥，Query2返回 n 行盖桥，如果行R在Query1中出現(xiàn)了x次灾螃，在Query2中出現(xiàn)了y次，則行R應(yīng)該在INTERSECT ALL運算之后出現(xiàn)minimum(x揩徊，y)次腰鬼。
下面提供用于T-SQL實現(xiàn)的INTERSECT ALL集合運算：公用表表達(dá)式 + 排名函數(shù)
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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
結(jié)果如下：

其中UK NULL London有四個重復(fù)行嵌赠，
在排序函數(shù)的OVER字句中使用 ORDER BY ( SELECT 常量> )可以告訴SQL Server不必在意行的順序。
四熄赡、EXCEPT（差集）集合運算
1.差集的文氏圖
差集：兩個集合（記為集合A和集合B）的由屬于集合A姜挺，但不屬于集合B的所有元素組成的集合。
圖中陰影區(qū)域代表集合A與集合B的差集

2.EXCEPT DISTINCT集合運算
（1）假設(shè)Query1返回 m 行彼硫，Query2返回 n 行炊豪，Query1和Query2有相同的 h 行，則Query1 INTERSECT Query2返回 m – h 行,而Query2 INTERSECT Query1 返回 n – h 行
（2）EXCEPT集合運算在邏輯上先刪除兩個輸入多集中的重復(fù)行（把多集轉(zhuǎn)變成集合）拧篮，然后返回只在第一個集合中出現(xiàn)词渤，在第二個集合眾不出現(xiàn)所有行。
（3）EXCEPT 會刪除重復(fù)行串绩，所以它的結(jié)果就是集合缺虐；
（4）EXCEPT是不對稱的，差集的結(jié)果取決于兩個查詢的前后關(guān)系礁凡。
（5）相同的行在結(jié)果中只出現(xiàn)一次高氮。
（6）不能顯示指定DISTINCT字句，如果不指定ALL顷牌，則默認(rèn)使用DISTINCT剪芍。
（7）當(dāng)Query1與Query2比較某行記錄是否相等時，會認(rèn)為取值為NULL的列是相等的列韧掩。
（8）用左外聯(lián)接或NOT EXISTS謂詞可以代替INTERSECT集合運算紊浩，但是必須對NULL進(jìn)行處理，否則這兩種方法對NULL值進(jìn)行比較時疗锐，比較結(jié)果都是UNKNOWN坊谁，這樣的行會被過濾掉。
3.EXCEPT ALL集合運算
（1）ANSI SQL支持帶有ALL選項的EXCEPT集合運算滑臊，但SQL Server2008現(xiàn)在還沒有實現(xiàn)這種運算口芍。后面會提供一種用于T-SQL實現(xiàn)的替代方案；
（2）假設(shè)Query1返回 m 行雇卷，Query2返回 n 行鬓椭，如果行R在Query1中出現(xiàn)了x次，在Query2中出現(xiàn)了y次,且x>y关划，則行R應(yīng)該在EXCEPT ALL運算之后出現(xiàn) x – y 次小染。
下面提供用于T-SQL實現(xiàn)的EXCEPT ALL集合運算：公用表表達(dá)式 + 排名函數(shù)
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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
結(jié)果如下：

五、集合運算的優(yōu)先級
1.INTERSECT>UNION=EXCEPT
2.首先計算INTERSECT,然后從左到右的出現(xiàn)順序依次處理優(yōu)先級的相同的運算贮折。
3.可以使用圓括號控制集合運算的優(yōu)先級裤翩，它具有最高的優(yōu)先級。
六调榄、特殊處理
1.只有ORDER BY能夠直接應(yīng)用于集合運算的結(jié)果踊赠；
2.其他階段如表運算符呵扛、WHERE、GROUP BY筐带、HAVING等今穿，不支持直接應(yīng)用于集合運算的結(jié)果，這個時候可以使用表表達(dá)式來避開這一限制伦籍。如根據(jù)包含集合運算的查詢定義個表表達(dá)式蓝晒，然后在外部查詢中對表表達(dá)式應(yīng)用任何需要的邏輯查詢處理；
3.ORDER BY字句不能直接應(yīng)用于集合運算中的單個查詢帖鸦，這個時候可以TOP+ORDER BY字句+表表達(dá)式來避開這一限制拔创。如定義一個基于該TOP查詢的表表達(dá)式，然后通過一個使用這個表表達(dá)式的外部查詢參與集合運算富蓄。
七剩燥、練習(xí)題
1.寫一個查詢，返回在2008年1月有訂單活動立倍，而在2008年2月沒有訂單活動的客戶和雇員灭红。
期望結(jié)果：

方案一：EXCEPT
（1）先用查詢1查詢出2008年1月份有訂單活動的客戶和雇員
（2）用查詢2查詢2008年2月份客戶的訂單活動的客戶和雇員
（3）用差集運算符查詢2008年1月有訂單活動而2008年2月沒有訂單活動的客戶和雇員
MySQL
1
2
3
4
5
6
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)行查詢寝志，因為比較NULL值的結(jié)果是UNKNOWN娇斑，這樣的行用NOT EXISTS查詢返回的子查詢的行會被過濾掉，所以最后的外查詢會多出NULL值的行材部，最后查詢結(jié)果中會多出NULL值的行毫缆。
MySQL
1
2
3
4
5
6
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’
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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’
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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
用方案一查詢出來結(jié)果為50行，會把cutid=NULL,empid=1的行過濾掉
用方案二查詢出來結(jié)果為51行乐导，不會把cutid=NULL,empid=1的行過濾掉
用下面的方案可以解決上面的問題苦丁，需要處理cutid=NULL,或者empid=null的情況。返回50行
MySQL
1
2
3
4
5
6
SELECT custid ,
empid
FROM Sales.Orders AS O1
WHERE orderdate >= '20080101'
AND orderdate = '20080201'
AND orderdate
2.寫一個查詢物臂，返回在2008年1月和在2008年2月都有訂單活動的客戶和雇員旺拉。
期望結(jié)果：

方案一：INTERSECT
（1）先用查詢1查詢出2008年1月份有訂單活動的客戶和雇員
（2）用查詢2查詢2008年2月份客戶的訂單活動的客戶和雇員
（3）用交集運算符查詢2008年1月和2008年2月都有訂單活動的客戶和雇員
MySQL
1
2
3
4
5
6
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)行查詢仪媒，因為比較NULL值的結(jié)果是UNKNOWN沉桌，這樣的行用EXISTS查詢返回的子查詢的行會被過濾掉，所以最后的外查詢會少NULL值的行，最后查詢結(jié)果中會少NULL值的行蒲牧。
MySQL
1
2
3
4
5
6
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′
用方案一查詢出來結(jié)果為6行，不會把cutid=NULL,empid=1的行過濾掉
用方案二查詢出來結(jié)果為5行赌莺，會把cutid=NULL,empid=1的行過濾掉
用下面的方案可以解決上面的問題冰抢，需要處理cutid=NULL,或者empid=null的情況。返回6行艘狭。
MySQL
1
2
3
4
5
6
SELECT custid ,
empid
FROM Sales.Orders AS O1
WHERE orderdate >= '20080101'
AND orderdate = '20080201'
AND orderdate
3.寫一個查詢挎扰，返回在2008年1月和在2008年2月都有訂單活動，而在2007年沒有訂單活動的客戶和雇員
期望結(jié)果：

方案一：INTERSECT + EXCEPT
MySQL
1
2
3
4
5
6
7
SELECT custid ,
empid
FROM Sales.Orders
WHERE orderdate >= '20080101'
AND orderdate = '20080201'
AND orderdate = '20070101'
AND orderdate
方案二：EXISTS + NOT EXISTS
MySQL
1
2
3
4
5
6
7
SELECT custid ,
empid
FROM Sales.Orders AS O1
WHERE orderdate >= '20080101'
AND orderdate = '20080201'
AND orderdate = '20070101'
AND orderdate