探討 T-SQL 的 EXISTS、EXCEPT、INTERSECT 算符

weixin_34120274發表於2011-10-01

本帖先探討 IT 人的職涯規劃,再回到技術面,研究 T-SQL 的 UNION、EXISTS、EXCEPT、INTERSECT 運算子。

之前有園友提到:「DBA 瞭解業務是非常必要的」,確實如此。不管您擔任的是 IT 的哪種職務,都應深入瞭解所任職公司的業務和流程,結合既有的 IT 專長,發展出自己在公司的「不可替代性」,甚至去思考老闆、公司的好高高層們,想要的是什麼? 若您是擔任 MIS 或 DBA,每天辛勤努力修機器、備份資料庫、維護網路狀態、研究最新的前臺和 .NET 技術,卻無法讓老闆瞭解你每天在忙什麼,則你的地位,可能遠不如一位很懂業務和流程的人,雖然他只會寫些 SQL 句子,卻能每天產出老闆和主管想看的報表,老闆不能沒有他,他也就創造了自己的不可替代性。 當然做專案的軟體公司,和一般產業的資訊部門又有所不同,這裡只是舉例。

那麼該學什麼技術最保值呢?有沒有什麼 IT 技術像會計學一樣,學一套就能吃一輩子?前臺 UI 技術日新月異、層出不窮,感覺自己再也跟不上年輕人熬夜學 ASP.NET 4,5,6,7,8 ...、Silverlight 5,6,7,8,9 ...? 若要論技術的保值性,將 SQL 和資料庫學到精通,或許是不錯的選擇。除了技術變化性比較小 (至少不會像某些技術會忽然推翻舊版本),往上鑽研可研究 BI、OLAP、Data Mining、報表、當顧問,往下鑽研可深入學 SQL 語句、軟體專案開發、備份還原和監控,橫向鑽研可學習作業系統、Storage、效能優化 .... 等等,至少就技術的「保值性」而言,學一套能「撐」較久,甚至大半輩子,也較有機會接觸公司的核心業務和流程 (把公司的業務邏輯都寫進儲存過程裡,公司從此不能沒有你)。

當然這裡只是舉例,並非鼓吹大家都該往此方向投入學習,就像有人說程式設計師最後都要升級 SA、PM,但這應視個人性格、興趣、所在的產業而論,並沒有標準的答案。但話說回來,要精通 SQL 的撰寫也非易事,其為易學難精的功夫,如筆者以前曾經轉貼的一篇陳年好文「程式設計師真情懺悔錄」也曾提到這些特性:

 

做應用軟體的人真的沒價值嗎?有些靠應用軟體賺了不少錢的朋友開始偷笑了。軟體必須加上專業知識,才能夠發揮價值;換句話說,如果您真的「只會寫程式碼」,卻沒有配合各種領域的專業知識 ,「程式設計」這項技能本身根本毫無價值可言。

就很像你的計算機裝了瀏覽器,但卻沒有對外聯機的網路一樣。如果沒有網路本身,瀏覽器一點價值也沒有,更不需要一家公司大費周章地利用平臺優勢去整倒另外一家公司 。


-你老覺得真正的高手應該精通各門各派的技術 (?),如果你會 XML,他不會,你就覺得你比他厲害。你覺得他的履歷上寫的技能太少,證照太少,所以你認為你比他優秀?


有人認為寫 Java 程式應該善用工具,用 UltraEdit 根本是重新造輪子的行為,所以一開始就學 JBuilder (或 Visual Studio) 的使用者,其實他用 JBuilder 寫了老半天 GUI 程式,哪天回頭叫他用文字編輯器寫個簡單的 Frame + Button 他卻寫不出來,因為他從沒弄懂過 Java 的事件處理模型。他只會不斷地:選擇元件 -> 放在容器裡 -> 調整位置和大小 -> 調整屬性 -> 按兩下 -> 填寫事件處理函式,成為一個名副其實的「程式碼女工」。


有人覺得他精通各家廠商的資料庫,所以看不起那些只會下 SQL 命令或是隻會寫 stored procedure 的人,因為他可是精通 ODBC、JDBC、ADO、ADO.NET 各種程式的寫法。問題是,一個精通 SQL 的專家,和只會寫 SQL 語句的人,在資料庫表格互動參考、資料量很大的時候,要從中取出我們需要的內容,所下的 SQL 指令在效率上,是幾秒鐘和幾個小時的差別。

SQL 也是個專門學問,要能夠巧妙地操作它,必須下非常多功夫做研究,而且一研究可能就是十幾年。如果貴公司的專案老是苦於資料庫訪問的效能不佳,你猜老闆會花錢找一個有能力徹底改善所有 SQL 語句中效能問題的稀有專家,還是再找一個號稱他什麼都會,結果一點用場也派不上的「資料庫女工」? 我們常常看到某人列出他的履歷,好像會很多就是很厲害。但是當我們完全深入一項技術時 (喔,我是說你真正下過苦功的時候),通常我們會越來越感覺到自己的渺小。



說偏了,迴歸技術面,以下才是本帖的主題。

-------------------------------------------------
本帖的示例下載:
http://files.cnblogs.com/WizardWu/111001.zip


SQL Server 2008 R2 的 AdventureWorks 資料庫下載:
http://msftdbprodsamples.codeplex.com/releases/view/55926
http://msftdbprodsamples.codeplex.com/
---------------------------------------------------

The EXISTS Operator

EXISTS 可稱之為運算子,有些書稱它為關鍵詞。 EXISTS 和 IN 關鍵詞很類似,但仍有些不同,EXISTS 使用時不會返回資料,而是返回簡單的 TRUE / FALSE。如下示例 [1],即使子查詢返回 NULL,用 EXISTS 也會得到 TRUE:
USE AdventureWorks2008 ;
GO

SELECT DepartmentID, Name 
FROM HumanResources.Department 
WHERE EXISTS (SELECT NULL)

 

EXISTS 和 IN 在很多情況下可查到相同的結果 [1],兩個關鍵詞的用途有些類似,因此網路上常有些 EXISTS 和 IN 誰效率較好的討論串 [2],此處不再贅述。 以下我們改探討 EXISTS 和 JOIN 的比較,下方是書上的兩個示例 [7],兩者的執行結果相同,第一個示例是 EXISTS 搭配「巢狀查詢 (nested query)」,第二個示例語法看起來較簡潔,是傳統大家習慣用的 join 寫法:


SELECT  e.BusinessEntityID,  FirstName,  LastName
FROM  HumanResources.Employee  e
JOIN  Person.Person  pp
ON  e.BusinessEntityID = pp.BusinessEntityID
WHERE  EXISTS 
  (SELECT  BusinessEntityID
  FROM  HumanResources.JobCandidate  jc
  WHERE  e.BusinessEntityID = jc.BusinessEntityID);

上方示例、下方示例,執行結果皆為:
EmployeeID  FirstName  LastName
-----------  ------------  -------------
  212     Peng     Wu
  274     Stephen   Jiang

 

(2 row(s) affected) 


SELECT  e.BusinessEntityID,  FirstName,  LastName
FROM  HumanResources.Employee  e
JOIN  Person.Person  pp
ON  e.BusinessEntityID = pp.BusinessEntityID
JOIN  HumanResources.JobCandidate  jc
ON  e.BusinessEntityID = jc.BusinessEntityID;

 

兩個示例雖然結果相同,但 EXISTS 的效能會較好 (書上說的) [7]。 當我們使用 EXISTS 關鍵詞時,SQL Server 不會用 full row-by-row join,而是在記錄當中搜尋,當它找到第一個符合條件的記錄時,就會立即停止後續搜尋的動作,並標示為 TRUE,表示不需要再往下找了;反觀 inner join 則不會有此種標示的動作。

此外,NOT EXISTS 也有此種標示的功能。當 NOT EXISTS 找到符合條件的資料時,同樣也會標示,但標示為 FALSE,表示不需要再往下找了。


除了此一效能差別外,EXISTS 和 join-based 在查詢時,各方面並無二致。



Using EXISTS in Other Ways

有時您會看到有些人執行 CREATE 前,會加一些古怪的語句,如下:


IF EXISTS (SELECT * FROM sysobjects WHERE id = 
 object_id(N'[Sales].[SalesOrderHeader]'AND OBJECTPROPERTY(id,N'IsUserTable'= 1)
DROP TABLE [Sales].[SalesOrderHeader]
GO

CREATE TABLE [Sales].[SalesOrderHeader] (
...
...

 

他們使用 sys.objects、sys.databases,或 INFORMATION_SCHEMA 檢視 (view),目的都一樣,想在執行 CREATE 前先看看某個物件是否已存在。 但同樣是用 EXISTS 做事先的判斷,我有更好的寫法,請參考以下建立資料庫的示例:


USE master
GO

IF NOT EXISTS (SELECT 'True' FROM sys.databases WHERE name = 'DBCreateTest')
BEGIN
  CREATE DATABASE DBCreateTest
END
ELSE
BEGIN
  PRINT '此資料庫已經存在。跳過了 CREATE DATABASE 語句'
END
GO

 

 第一次執行此語句時,若沒有 DBCreateTest 資料庫,則建立它;第二次執行時,由於該資料庫已存在,因此會印出自定義的提示資訊。 因此,僅用一點小技巧,可避免掉不必要的 DROP 動作被執行,這會讓您公司的產品在被安裝時更有效率。

EXISTS 是很方便的關鍵詞,有時可讓查詢語句執行得更有效率,有時則可簡化 SQL 語句。
 

The INTERSECT and EXCEPT Operators

 接下來介紹的 INTERSECT 和 EXCEPT 關鍵詞,在 SQL Server 和其他廠牌資料庫多半都支援。 INTERSECT 和 EXCEPT 在處理兩個 result set 時,和 UNION 關鍵詞很類似。 在 MSDN、TechNet 上雖然有對這兩個關鍵詞作解釋 [3], [6],但半機器的翻譯有些不易理解,建議參考下列取自書上的圖文示例 [7],可讓人一目瞭然:

  • UNION: 將多個「結果集 (result set)」的「行 (row)」合併,作為單個結果集返回,並移除重複的行。若有重複的行,只留下一個。
  • UNION ALL: 將多個「結果集 (result set)」所有的行合併,不論是否有重複的行。
  • EXCEPT: 提取只在 EXCEPT 左側存在,但右側不存在的行,參考下圖 1。用更口語化的說法:「只給我 A 裡才有,但 B 裡沒有的行」。
  • INTERSECT: 只提取兩個結果集裡,都存在的行。 INTERSECT 很類似 inner join,但 INTERSECT 並不會對特定的「列 (column)」去做處理。

由於上述關鍵詞,不會對特定的「列」去做處理,因此在使用上必須符合某些原則 [3],例如:所有查詢中的列數和列的順序必須相同、資料型別必須相容。


圖 1 UNION 中若有重複的行,會被移除,只留下一個

 

在看本文最後的完整比較示例前,我們先了解 EXCEPT 和 INTERSECT 的基本語法。

EXCEPT


EXCEPT 用法如下,簡單易懂:

<table or tabular result>
EXCEPT
<table or tabular result with same number of columns and type as top query>

 

同樣的查詢需求,我們改用 NOT EXISTS 的寫法,也能得到和 EXCEPT 寫法相同的結果,用法如下 [1], [7]。 本文最後會有完整的示例。

<base query>
WHERE NOT EXISTS
  (SELECT 1
    FROM <table or result with same number of columns and type as top query>
    WHERE <base query first column> = <comparison table first column> [, ...])

 

INTERSECT

至於 INTERSECT 的語法也一樣簡單易懂,用法和 EXCEPT、UNION 也都類似,如下:

<table or tabular result>
INTERSECT
<table or tabular result with same number of columns and type as top query>


同樣的查詢需求,我們改用 EXISTS 的寫法,也能得到和 INTERSECT 寫法相同的結果,用法如下 [1], [7]。 本文最後會有完整的示例。


<base query>
WHERE EXISTS
  (SELECT 1
    FROM <table or result with same number of columns and type as top query>
    WHERE <base query first column> = <comparison table first column> [, ...])

 

 此外,INTERSECT 和 EXCEPT 可同時混搭使用,但 INTERSECT 運算子會優先於 EXCEPT [6]

SELECT * FROM TableA 
EXCEPT 
SELECT * FROM TableB 
INTERSECT 
SELECT * FROM TableC



Comparing EXCEPT and INTERSECT with Their EXISTS and NOT EXISTS Equivalents

 以下的示例,我們將演示本帖前述的所有內容,先看 EXCEPT、INTERSECT 的示例,再看與他們倆對等的 NOT EXISTS、EXISTS 寫法示例 (不同寫法,但會返回相同的結果集)。


ExpandedBlockStart.gif完整示例
 1 -- Create our test tables and populate them with a few relevant rows
 2 CREATE TABLE UnionTest1
 3 (
 4   idcol int IDENTITY,
 5   col2 char(3),
 6 );
 7 
 8 CREATE TABLE UnionTest2
 9 (
10   idcol int IDENTITY,
11   col4 char(3),
12 );
13 
14 INSERT INTO UnionTest1 VALUES ('AAA'),('BBB'),('CCC');
15 INSERT INTO UnionTest2 VALUES ('CCC'),('DDD'),('EEE');
16 
17 
18 PRINT '測試 1, Source and content of both tables:';
19 
20 SELECT 1 AS SourceTable, col2 AS Value FROM UnionTest1
21 UNION ALL
22 SELECT 2, col4 FROM UnionTest2;
23 
24 
25 PRINT '測試 2, Results with classic UNION';
26 
27 SELECT col2 FROM UnionTest1
28 UNION
29 SELECT col4 FROM UnionTest2;
30 
31 
32 PRINT '測試 3-1, Results with EXCEPT';
33 
34 SELECT col2 FROM UnionTest1
35 EXCEPT
36 SELECT col4 FROM UnionTest2;
37 
38 
39 PRINT '測試 3-2, Equivilent of EXCEPT but using NOT EXISTS';
40 
41 SELECT col2 FROM UnionTest1 ut1
42 WHERE NOT EXISTS
43  (SELECT col4 FROM UnionTest2 WHERE col4 = ut1.col2);
44 
45 
46 PRINT '測試 4-1, Results with INTERSECT';
47 
48 SELECT col2 FROM UnionTest1
49 INTERSECT
50 SELECT col4 FROM UnionTest2;
51 
52 
53 PRINT '測試 4-2, Equivilent of INTERSECT but using EXISTS';
54 
55 SELECT col2 FROM UnionTest1 ut1
56 WHERE EXISTS
57  (SELECT col4 FROM UnionTest2 WHERE col4 = ut1.col2);
58  
59 
60 PRINT '測試 5, INTERSECT 運算子優先於 EXCEPT';
61 SELECT col2 FROM UnionTest1
62 EXCEPT
63 SELECT col2 FROM UnionTest1
64 INTERSECT
65 SELECT col4 FROM UnionTest2;
66 
67 
68 -- Clean up after ourselves
69 DROP TABLE UnionTest1;
70 DROP TABLE UnionTest2;

 

執行結果:

測試 1 結果          

測試 2 結果       

測試 3-1、3-2 結果     

測試 4-1、4-2 結果     

測試 5 結果

如您所見,測試 3-1、3-2 的結果相同,測試 4-1、4-2 的結果也相同,但 SQL Server 的「評估的執行計劃」很不同,且 EXCEPT / INTERSECT 的「查詢開銷」會比 NOT EXISTS / EXISTS 大很多,如同上例中,測試 3-1 比 3-2 的效能差,測試 4-1 比 4-2 效能差。而且經我測試 (書籍作者 [7]),大多數的情形,用 EXISTS 的寫法,效能都比 EXCEPT / INTERSECT 要好。

雖然如此,但我們不該完全捨棄 EXCEPT / INTERSECT 的使用,因為他們的語法簡潔、容易閱讀。因此在您的 SQL 語句中,除非兩種寫法的效能差距很大,不然我仍建議使用 EXCEPT / INTERSECT 寫法,以便專案後續的維護。若論 EXISTS 和 EXCEPT / INTERSECT 哪一種寫法較好,則屬見仁見智的問題,端視您的資料量、執行環境、實測兩種寫法的效率差距,來決定要用哪一種寫法。




參考文件:

[1] EXISTS (Transact-SQL)
http://msdn.microsoft.com/zh-cn/library/ms188336.aspx

[2] SQL 中 IN 和 EXISTS 用法的區別?
http://topic.csdn.net/u/20090715/10/ec21e6cc-7265-4c44-a35c-8a0003e73978.html

[3] EXCEPT 和 INTERSECT (Transact-SQL)
http://msdn.microsoft.com/zh-cn/library/ms188055.aspx

[4] UNION (Transact-SQL)
http://msdn.microsoft.com/zh-cn/library/ms180026.aspx

[5] 與其他 Transact-SQL 語句一起使用 UNION、EXCEPT 和 INTERSECT
http://msdn.microsoft.com/zh-cn/library/ms191523.aspx

[6] 使用 EXCEPT 和 INTERSECT 執行半聯接操作
http://msdn.microsoft.com/zh-cn/library/ms191255.aspx



參考書籍:

 [7] Professional Microsoft SQL Server 2008 Programming, Chapter 3
http://www.wrox.com/WileyCDA/WroxTitle/Professional-Microsoft-SQL-Server-2008-Programming.productCd-0470257024.html
http://www.ppurl.com/pdfpreview/?skey=AQQHNgokAXRXMVA7UAxQP1Z0XTkJYwBtB2ZUZQI6UzQ%3D&page=0



相關文章:

 [8] 探討 .NET 4 新增的 SortedSet 類 (.NET 平臺的 EXCEPT、INTERSECT)
http://www.cnblogs.com/WizardWu/archive/2010/06/17/1759297.html


  

相關文章