本帖先探討 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:
GO
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE EXISTS (SELECT NULL)
EXISTS 和 IN 在很多情況下可查到相同的結果 [1],兩個關鍵詞的用途有些類似,因此網路上常有些 EXISTS 和 IN 誰效率較好的討論串 [2],此處不再贅述。 以下我們改探討 EXISTS 和 JOIN 的比較,下方是書上的兩個示例 [7],兩者的執行結果相同,第一個示例是 EXISTS 搭配「巢狀查詢 (nested query)」,第二個示例語法看起來較簡潔,是傳統大家習慣用的 join 寫法:
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)
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 前,會加一些古怪的語句,如下:
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 做事先的判斷,我有更好的寫法,請參考以下建立資料庫的示例:
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]。 本文最後會有完整的示例。
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]。 本文最後會有完整的示例。
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]:
EXCEPT
SELECT * FROM TableB
INTERSECT
SELECT * FROM TableC
Comparing EXCEPT and INTERSECT with Their EXISTS and NOT EXISTS Equivalents |
以下的示例,我們將演示本帖前述的所有內容,先看 EXCEPT、INTERSECT 的示例,再看與他們倆對等的 NOT EXISTS、EXISTS 寫法示例 (不同寫法,但會返回相同的結果集)。
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