今天就跟大家聊聊有關(guān)如何理解SQL語句的集合運(yùn)算,可能很多人都不太了解,為了讓大家更加了解,小編給大家總結(jié)了以下內(nèi)容,希望大家根據(jù)這篇文章可以有所收獲。
十多年的蒸湘網(wǎng)站建設(shè)經(jīng)驗(yàn),針對設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對一服務(wù),響應(yīng)快,48小時(shí)及時(shí)工作處理。全網(wǎng)營銷推廣的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同,自動調(diào)整蒸湘建站的顯示方式,使網(wǎng)站能夠適用不同顯示終端,在瀏覽器中調(diào)整網(wǎng)站的寬度,無論在任何一種瀏覽器上瀏覽網(wǎng)站,都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì),從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)公司從事“蒸湘網(wǎng)站設(shè)計(jì)”,“蒸湘網(wǎng)站推廣”以來,每個客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。
以前總是追求新東西,發(fā)現(xiàn)基礎(chǔ)才是最重要的,今年主要的目標(biāo)是精通SQL查詢和SQL性能優(yōu)化。
概述
主要是對集合運(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ù)庫,創(chuàng)建表,插入數(shù)據(jù)。
一、集合運(yùn)算
1.集合運(yùn)算
(1)對輸入的兩個集合或多集進(jìn)行的運(yùn)算。
(2)多集:由兩個輸入的查詢生成的可能包含重復(fù)記錄的中間結(jié)果集。
(3)T-SQL支持三種集合運(yùn)算:并集(UNION)、交集(INTERSECT)、差集(EXCEPT)
2.語法
集合運(yùn)算的基本格式:
輸入的查詢1
集合運(yùn)算符>
輸入的查詢2
[ORDER BY]
3.要求
(1)輸入的查詢不能包含ORDER BY字句;
(2)可以為整個集合運(yùn)算結(jié)果選擇性地增加一個ORDER BY字句;
(3)每個單獨(dú)的查詢可以包含所有邏輯查詢處理階段(處理控制排列順序的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)換日期和/或時(shí)間時(shí),轉(zhuǎn)換失敗;
(6)集合運(yùn)算結(jié)果中列名由輸入的查詢1決定,如果要為結(jié)果分配結(jié)果列,應(yīng)該在輸入的查詢1中分配相應(yīng)的別名;
(7)集合運(yùn)算時(shí),對行進(jìn)行進(jìn)行比較時(shí),集合運(yùn)算認(rèn)為兩個NULL相等;
(8)UNION支持DISTINCT和ALL。不能顯示指定DISTINCT字句,如果不指定ALL,則默認(rèn)使用DISTINCT;
(9)INTERSET和EXCEPT默認(rèn)使用DISTINCT,不支持ALL。
二、UNION(并集)集合運(yùn)算
1.并集的文氏圖
并集:兩個集合的并集是一個包含集合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 不會刪除重復(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 會刪除重復(fù)行,所以它的結(jié)果就是集合;
(3)相同的行在結(jié)果中只出現(xiàn)一次。
(4)不能顯示指定DISTINCT字句,如果不指定ALL,則默認(rèn)使用DISTINCT。
(5)當(dāng)Query1與Query2比較某行記錄是否相等時(shí),會認(rèn)為取值為NULL的列是相等的列。
三、INTERSECT(交集)集合運(yùn)算
1.交集的文氏圖
交集:兩個集合(記為集合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)算在邏輯上首先刪除兩個輸入多集中的重復(fù)行(把多集變?yōu)榧?,然后返回只在兩個集合中都出現(xiàn)的行;
(3)INTERSECT 會刪除重復(fù)行,所以它的結(jié)果就是集合;
(4)相同的行在結(jié)果中只出現(xiàn)一次。
(5)不能顯示指定DISTINCT字句,如果不指定ALL,則默認(rèn)使用DISTINCT。
(6)當(dāng)Query1與Query2比較某行記錄是否相等時(shí),會認(rèn)為取值為NULL的列是相等的列。
(7)用內(nèi)聯(lián)接或EXISTS謂詞可以代替INTERSECT集合運(yùn)算,但是必須對NULL進(jìn)行處理,否則這兩種方法對NULL值進(jìn)行比較時(shí),比較結(jié)果都是UNKNOWN,這樣的行會被過濾掉。
3.INTERSECT ALL集合運(yùn)算
(1)ANSI SQL支持帶有ALL選項(xiàng)的INTERSECT集合運(yùn)算,但SQL Server2008現(xiàn)在還沒有實(shí)現(xiàn)這種運(yùn)算。后面會提供一種用于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有四個重復(fù)行,
在排序函數(shù)的OVER字句中使用 ORDER BY ( SELECT 常量> )可以告訴SQL Server不必在意行的順序。
四、EXCEPT(差集)集合運(yùn)算
1.差集的文氏圖
差集:兩個集合(記為集合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)算在邏輯上先刪除兩個輸入多集中的重復(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比較某行記錄是否相等時(shí),會認(rèn)為取值為NULL的列是相等的列。
(8)用左外聯(lián)接或NOT EXISTS謂詞可以代替INTERSECT集合運(yùn)算,但是必須對NULL進(jìn)行處理,否則這兩種方法對NULL值進(jìn)行比較時(shí),比較結(jié)果都是UNKNOWN,這樣的行會被過濾掉。
3.EXCEPT ALL集合運(yùn)算
(1)ANSI SQL支持帶有ALL選項(xiàng)的EXCEPT集合運(yùn)算,但SQL Server2008現(xiàn)在還沒有實(shí)現(xiàn)這種運(yùn)算。后面會提供一種用于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ù)
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ùn)算的優(yōu)先級
1.INTERSECT>UNION=EXCEPT
2.首先計(jì)算INTERSECT,然后從左到右的出現(xiàn)順序依次處理優(yōu)先級的相同的運(yùn)算。
3.可以使用圓括號控制集合運(yùn)算的優(yōu)先級,它具有***的優(yōu)先級。
六、特殊處理
1.只有ORDER BY能夠直接應(yīng)用于集合運(yùn)算的結(jié)果;
2.其他階段如表運(yùn)算符、WHERE、GROUP BY、HAVING等,不支持直接應(yīng)用于集合運(yùn)算的結(jié)果,這個時(shí)候可以使用表表達(dá)式來避開這一限制。如根據(jù)包含集合運(yùn)算的查詢定義個表表達(dá)式,然后在外部查詢中對表表達(dá)式應(yīng)用任何需要的邏輯查詢處理;
3.ORDER BY字句不能直接應(yīng)用于集合運(yùn)算中的單個查詢,這個時(shí)候可以TOP+ORDER BY字句+表表達(dá)式來避開這一限制。如定義一個基于該TOP查詢的表表達(dá)式,然后通過一個使用這個表表達(dá)式的外部查詢參與集合運(yùn)算。
七、練習(xí)題
1.寫一個查詢,返回在2008年1月有訂單活動,而在2008年2月沒有訂單活動的客戶和雇員。
期望結(jié)果:
方案一:EXCEPT
(1)先用查詢1查詢出2008年1月份有訂單活動的客戶和雇員
(2)用查詢2查詢2008年2月份客戶的訂單活動的客戶和雇員
(3)用差集運(yùn)算符查詢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進(jìn)行查詢,如果custid或empid其中有null值存在,則不能用NOT EXISTS進(jìn)行查詢,因?yàn)楸容^NULL值的結(jié)果是UNKNOWN,這樣的行用NOT EXISTS查詢返回的子查詢的行會被過濾掉,所以***的外查詢會多出NULL值的行,***查詢結(jié)果中會多出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
用方案一查詢出來結(jié)果為50行,會把cutid=NULL,empid=1的行過濾掉
用方案二查詢出來結(jié)果為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月都有訂單活動的客戶和雇員。
期望結(jié)果:
方案一:INTERSECT
(1)先用查詢1查詢出2008年1月份有訂單活動的客戶和雇員
(2)用查詢2查詢2008年2月份客戶的訂單活動的客戶和雇員
(3)用交集運(yùn)算符查詢2008年1月和2008年2月都有訂單活動的客戶和雇員
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查詢返回的子查詢的行會被過濾掉,所以***的外查詢會少NULL值的行,***查詢結(jié)果中會少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′
插入cutid=NULL,empid=1,orderdate=’20080201′
用方案一查詢出來結(jié)果為6行,不會把cutid=NULL,empid=1的行過濾掉
用方案二查詢出來結(jié)果為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年沒有訂單活動的客戶和雇員
期望結(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
看完上述內(nèi)容,你們對如何理解SQL語句的集合運(yùn)算有進(jìn)一步的了解嗎?如果還想了解更多知識或者相關(guān)內(nèi)容,請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道,感謝大家的支持。
本文名稱:如何理解SQL語句的集合運(yùn)算
文章網(wǎng)址:http://bm7419.com/article46/jdjohg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供移動網(wǎng)站建設(shè)、小程序開發(fā)、網(wǎng)站策劃、網(wǎng)站維護(hù)、外貿(mào)建站、網(wǎng)站設(shè)計(jì)公司
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)