本帖先探討 IT 人的職涯規(guī)劃，再回到技術(shù)面，研究 T-SQL 的 UNION、EXISTS、EXCEPT、INTERSECT 運(yùn)算符。
之前有園友提到：「DBA 了解業(yè)務(wù)是非常必要的」，確實(shí)如此。不管您擔(dān)任的是 IT 的哪種職務(wù)，都應(yīng)深入了解所任職公司的業(yè)務(wù)和流程，結(jié)合既有的 IT 專(zhuān)長(zhǎng)，發(fā)展出自己在公司的「不可替代性」，甚至去思考老板、公司的好高高層們，想要的是什么？ 若您是擔(dān)任 MIS 或 DBA，每天辛勤努力修機(jī)器、備份數(shù)據(jù)庫(kù)、維護(hù)網(wǎng)絡(luò)狀態(tài)、研究最新的前臺(tái)和 .NET 技術(shù)，卻無(wú)法讓老板了解你每天在忙什么，則你的地位，可能遠(yuǎn)不如一位很懂業(yè)務(wù)和流程的人，雖然他只會(huì)寫(xiě)些 SQL 句子，卻能每天產(chǎn)出老板和主管想看的報(bào)表，老板不能沒(méi)有他，他也就創(chuàng)造了自己的不可替代性。 當(dāng)然做項(xiàng)目的軟件公司，和一般產(chǎn)業(yè)的信息部門(mén)又有所不同，這里只是舉例。
那么該學(xué)什么技術(shù)最保值呢？有沒(méi)有什么 IT 技術(shù)像會(huì)計(jì)學(xué)一樣，學(xué)一套就能吃一輩子？前臺(tái) UI 技術(shù)日新月異、層出不窮，感覺(jué)自己再也跟不上年輕人熬夜學(xué) ASP.NET 4,5,6,7,8 ...、Silverlight 5,6,7,8,9 ...？ 若要論技術(shù)的保值性，將 SQL 和數(shù)據(jù)庫(kù)學(xué)到精通，或許是不錯(cuò)的選擇。除了技術(shù)變化性比較小 (至少不會(huì)像某些技術(shù)會(huì)忽然推翻舊版本)，往上鉆研可研究 BI、OLAP、Data Mining、報(bào)表、當(dāng)顧問(wèn)，往下鉆研可深入學(xué) SQL 語(yǔ)句、軟件項(xiàng)目開(kāi)發(fā)、備份還原和監(jiān)控，橫向鉆研可學(xué)習(xí)操作系統(tǒng)、Storage、性能優(yōu)化 .... 等等，至少就技術(shù)的「保值性」而言，學(xué)一套能「撐」較久，甚至大半輩子，也較有機(jī)會(huì)接觸公司的核心業(yè)務(wù)和流程 (把公司的業(yè)務(wù)邏輯都寫(xiě)進(jìn)存儲(chǔ)過(guò)程里，公司從此不能沒(méi)有你)。
當(dāng)然這里只是舉例，并非鼓吹大家都該往此方向投入學(xué)習(xí)，就像有人說(shuō)程序員最后都要升級(jí) SA、PM，但這應(yīng)視個(gè)人性格、興趣、所在的產(chǎn)業(yè)而論，并沒(méi)有標(biāo)準(zhǔn)的答案。但話(huà)說(shuō)回來(lái)，要精通 SQL 的撰寫(xiě)也非易事，其為易學(xué)難精的功夫，如筆者以前曾經(jīng)轉(zhuǎn)貼的一篇陳年好文「程序員真情懺悔錄」也曾提到這些特性：
做應(yīng)用軟件的人真的沒(méi)價(jià)值嗎？有些靠應(yīng)用軟件賺了不少錢(qián)的朋友開(kāi)始偷笑了。軟件必須加上專(zhuān)業(yè)知識(shí)，才能夠發(fā)揮價(jià)值；換句話(huà)說(shuō)，如果您真的「只會(huì)寫(xiě)代碼」，卻沒(méi)有配合各種領(lǐng)域的專(zhuān)業(yè)知識(shí) ，「編程」這項(xiàng)技能本身根本毫無(wú)價(jià)值可言。
就很像你的計(jì)算機(jī)裝了瀏覽器，但卻沒(méi)有對(duì)外聯(lián)機(jī)的網(wǎng)絡(luò)一樣。如果沒(méi)有網(wǎng)絡(luò)本身，瀏覽器一點(diǎn)價(jià)值也沒(méi)有，更不需要一家公司大費(fèi)周章地利用平臺(tái)優(yōu)勢(shì)去整倒另外一家公司 。
-你老覺(jué)得真正的高手應(yīng)該精通各門(mén)各派的技術(shù) (?)，如果你會(huì) XML，他不會(huì)，你就覺(jué)得你比他厲害。你覺(jué)得他的履歷上寫(xiě)的技能太少，證照太少，所以你認(rèn)為你比他優(yōu)秀？
有人認(rèn)為寫(xiě) Java 程序應(yīng)該善用工具，用 UltraEdit 根本是重新造輪子的行為，所以一開(kāi)始就學(xué) JBuilder (或 Visual Studio) 的使用者，其實(shí)他用 JBuilder 寫(xiě)了老半天 GUI 程序，哪天回頭叫他用文本編輯器寫(xiě)個(gè)簡(jiǎn)單的 Frame + Button 他卻寫(xiě)不出來(lái)，因?yàn)樗麖臎](méi)弄懂過(guò) Java 的事件處理模型。他只會(huì)不斷地：選擇組件 -> 放在容器里 -> 調(diào)整位置和大小 -> 調(diào)整屬性 -> 按兩下 -> 填寫(xiě)事件處理函數(shù)，成為一個(gè)名副其實(shí)的「代碼女工」。
有人覺(jué)得他精通各家廠商的數(shù)據(jù)庫(kù)，所以看不起那些只會(huì)下 SQL 命令或是只會(huì)寫(xiě) stored procedure 的人，因?yàn)樗墒蔷?ODBC、JDBC、ADO、ADO.NET 各種程序的寫(xiě)法。問(wèn)題是，一個(gè)精通 SQL 的專(zhuān)家，和只會(huì)寫(xiě) SQL 語(yǔ)句的人，在數(shù)據(jù)庫(kù)表格交互參考、數(shù)據(jù)量很大的時(shí)候，要從中取出我們需要的內(nèi)容，所下的 SQL 指令在效率上，是幾秒鐘和幾個(gè)小時(shí)的差別。
SQL 也是個(gè)專(zhuān)門(mén)學(xué)問(wèn)，要能夠巧妙地操作它，必須下非常多功夫做研究，而且一研究可能就是十幾年。如果貴公司的項(xiàng)目老是苦于數(shù)據(jù)庫(kù)訪問(wèn)的性能不佳，你猜老板會(huì)花錢(qián)找一個(gè)有能力徹底改善所有 SQL 語(yǔ)句中性能問(wèn)題的稀有專(zhuān)家，還是再找一個(gè)號(hào)稱(chēng)他什么都會(huì)，結(jié)果一點(diǎn)用場(chǎng)也派不上的「數(shù)據(jù)庫(kù)女工」？ 我們常常看到某人列出他的履歷，好像會(huì)很多就是很厲害。但是當(dāng)我們完全深入一項(xiàng)技術(shù)時(shí) (喔，我是說(shuō)你真正下過(guò)苦功的時(shí)候)，通常我們會(huì)越來(lái)越感覺(jué)到自己的渺小。
說(shuō)偏了，回歸技術(shù)面，以下才是本帖的主題。
-------------------------------------------------
本帖的示例下載：
https://files.cnblogs.com/WizardWu/111001.zip
SQL Server 2008 R2 的 AdventureWorks 數(shù)據(jù)庫(kù)下載：
http://msftdbprodsamples.codeplex.com/releases/view/55926
http://msftdbprodsamples.codeplex.com/
---------------------------------------------------
| The EXISTS Operator |
EXISTS 可稱(chēng)之為運(yùn)算符，有些書(shū)稱(chēng)它為關(guān)鍵詞。 EXISTS 和 IN 關(guān)鍵詞很類(lèi)似，但仍有些不同，EXISTS 使用時(shí)不會(huì)返回?cái)?shù)據(jù)，而是返回簡(jiǎn)單的 TRUE / FALSE。如下示例 [1]，即使子查詢(xún)返回 NULL，用 EXISTS 也會(huì)得到 TRUE：
GO
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE EXISTS (SELECT NULL)
EXISTS 和 IN 在很多情況下可查到相同的結(jié)果 [1]，兩個(gè)關(guān)鍵詞的用途有些類(lèi)似，因此網(wǎng)絡(luò)上常有些 EXISTS 和 IN 誰(shuí)效率較好的討論串 [2]，此處不再贅述。 以下我們改探討 EXISTS 和 JOIN 的比較，下方是書(shū)上的兩個(gè)示例 [7]，兩者的執(zhí)行結(jié)果相同，第一個(gè)示例是 EXISTS 搭配「嵌套查詢(xún) (nested query)」，第二個(gè)示例語(yǔ)法看起來(lái)較簡(jiǎn)潔，是傳統(tǒng)大家習(xí)慣用的 join 寫(xiě)法：
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);
FROM HumanResources.Employee e
JOIN Person.Person pp
ON e.BusinessEntityID = pp.BusinessEntityID
JOIN HumanResources.JobCandidate jc
ON e.BusinessEntityID = jc.BusinessEntityID;
兩個(gè)示例雖然結(jié)果相同，但 EXISTS 的性能會(huì)較好 (書(shū)上說(shuō)的) [7]。 當(dāng)我們使用 EXISTS 關(guān)鍵詞時(shí)，SQL Server 不會(huì)用 full row-by-row join，而是在記錄當(dāng)中搜尋，當(dāng)它找到第一個(gè)符合條件的記錄時(shí)，就會(huì)立即停止后續(xù)搜尋的動(dòng)作，并標(biāo)示為 TRUE，表示不需要再往下找了；反觀 inner join 則不會(huì)有此種標(biāo)示的動(dòng)作。
此外，NOT EXISTS 也有此種標(biāo)示的功能。當(dāng) NOT EXISTS 找到符合條件的數(shù)據(jù)時(shí)，同樣也會(huì)標(biāo)示，但標(biāo)示為 FALSE，表示不需要再往下找了。
除了此一性能差別外，EXISTS 和 join-based 在查詢(xún)時(shí)，各方面并無(wú)二致。
| Using EXISTS in Other Ways |
有時(shí)您會(huì)看到有些人執(zhí)行 CREATE 前，會(huì)加一些古怪的語(yǔ)句，如下：
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)，目的都一樣，想在執(zhí)行 CREATE 前先看看某個(gè)對(duì)象是否已存在。 但同樣是用 EXISTS 做事先的判斷，我有更好的寫(xiě)法，請(qǐng)參考以下創(chuàng)建數(shù)據(jù)庫(kù)的示例：
GO
IF NOT EXISTS (SELECT 'True' FROM sys.databases WHERE name = 'DBCreateTest')
BEGIN
CREATE DATABASE DBCreateTest
END
ELSE
BEGIN
PRINT '此數(shù)據(jù)庫(kù)已經(jīng)存在。跳過(guò)了 CREATE DATABASE 語(yǔ)句'
END
GO
第一次執(zhí)行此語(yǔ)句時(shí)，若沒(méi)有 DBCreateTest 數(shù)據(jù)庫(kù)，則創(chuàng)建它；第二次執(zhí)行時(shí)，由于該數(shù)據(jù)庫(kù)已存在，因此會(huì)印出自定義的提示信息。 因此，僅用一點(diǎn)小技巧，可避免掉不必要的 DROP 動(dòng)作被執(zhí)行，這會(huì)讓您公司的產(chǎn)品在被安裝時(shí)更有效率。
EXISTS 是很方便的關(guān)鍵詞，有時(shí)可讓查詢(xún)語(yǔ)句執(zhí)行得更有效率，有時(shí)則可簡(jiǎn)化 SQL 語(yǔ)句。
| The INTERSECT and EXCEPT Operators |
接下來(lái)介紹的 INTERSECT 和 EXCEPT 關(guān)鍵詞，在 SQL Server 和其他廠牌數(shù)據(jù)庫(kù)多半都支持。 INTERSECT 和 EXCEPT 在處理兩個(gè) result set 時(shí)，和 UNION 關(guān)鍵詞很類(lèi)似。 在 MSDN、TechNet 上雖然有對(duì)這兩個(gè)關(guān)鍵詞作解釋 [3], [6]，但半機(jī)器的翻譯有些不易理解，建議參考下列取自書(shū)上的圖文示例 [7]，可讓人一目了然：
- UNION： 將多個(gè)「結(jié)果集 (result set)」的「行 (row)」合并，作為單個(gè)結(jié)果集返回，并移除重復(fù)的行。若有重復(fù)的行，只留下一個(gè)。
- UNION ALL： 將多個(gè)「結(jié)果集 (result set)」所有的行合并，不論是否有重復(fù)的行。
- EXCEPT： 提取只在 EXCEPT 左側(cè)存在，但右側(cè)不存在的行，參考下圖 1。用更口語(yǔ)化的說(shuō)法：「只給我 A 里才有，但 B 里沒(méi)有的行」。
- INTERSECT： 只提取兩個(gè)結(jié)果集里，都存在的行。 INTERSECT 很類(lèi)似 inner join，但 INTERSECT 并不會(huì)對(duì)特定的「列 (column)」去做處理。
由于上述關(guān)鍵詞，不會(huì)對(duì)特定的「列」去做處理，因此在使用上必須符合某些原則 [3]，例如：所有查詢(xún)中的列數(shù)和列的順序必須相同、數(shù)據(jù)類(lèi)型必須兼容。
圖 1 UNION 中若有重復(fù)的行，會(huì)被移除，只留下一個(gè)
在看本文最后的完整比較示例前，我們先了解 EXCEPT 和 INTERSECT 的基本語(yǔ)法。
| EXCEPT |
EXCEPT 用法如下，簡(jiǎn)單易懂：
<table or tabular result>
EXCEPT
<table or tabular result with same number of columns and type as top query>
同樣的查詢(xún)需求，我們改用 NOT EXISTS 的寫(xiě)法，也能得到和 EXCEPT 寫(xiě)法相同的結(jié)果，用法如下 [1], [7]。 本文最后會(huì)有完整的示例。
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 的語(yǔ)法也一樣簡(jiǎn)單易懂，用法和 EXCEPT、UNION 也都類(lèi)似，如下：
<table or tabular result>
INTERSECT
<table or tabular result with same number of columns and type as top query>
同樣的查詢(xún)需求，我們改用 EXISTS 的寫(xiě)法，也能得到和 INTERSECT 寫(xiě)法相同的結(jié)果，用法如下 [1], [7]。 本文最后會(huì)有完整的示例。
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 可同時(shí)混搭使用，但 INTERSECT 運(yùn)算符會(huì)優(yōu)先于 EXCEPT [6]：
EXCEPT
SELECT * FROM TableB
INTERSECT
SELECT * FROM TableC
| Comparing EXCEPT and INTERSECT with Their EXISTS and NOT EXISTS Equivalents |
以下的示例，我們將演示本帖前述的所有內(nèi)容，先看 EXCEPT、INTERSECT 的示例，再看與他們倆對(duì)等的 NOT EXISTS、EXISTS 寫(xiě)法示例 (不同寫(xiě)法，但會(huì)返回相同的結(jié)果集)。
完整示例
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 '測(cè)試 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 '測(cè)試 2, Results with classic UNION';
26
27 SELECT col2 FROM UnionTest1
28 UNION
29 SELECT col4 FROM UnionTest2;
30
31
32 PRINT '測(cè)試 3-1, Results with EXCEPT';
33
34 SELECT col2 FROM UnionTest1
35 EXCEPT
36 SELECT col4 FROM UnionTest2;
37
38
39 PRINT '測(cè)試 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 '測(cè)試 4-1, Results with INTERSECT';
47
48 SELECT col2 FROM UnionTest1
49 INTERSECT
50 SELECT col4 FROM UnionTest2;
51
52
53 PRINT '測(cè)試 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 '測(cè)試 5, INTERSECT 運(yùn)算符優(yōu)先于 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;
如您所見(jiàn)，測(cè)試 3-1、3-2 的結(jié)果相同，測(cè)試 4-1、4-2 的結(jié)果也相同，但 SQL Server 的「評(píng)估的執(zhí)行計(jì)劃」很不同，且 EXCEPT / INTERSECT 的「查詢(xún)開(kāi)銷(xiāo)」會(huì)比 NOT EXISTS / EXISTS 大很多，如同上例中，測(cè)試 3-1 比 3-2 的性能差，測(cè)試 4-1 比 4-2 性能差。而且經(jīng)我測(cè)試 (書(shū)籍作者 [7])，大多數(shù)的情形，用 EXISTS 的寫(xiě)法，性能都比 EXCEPT / INTERSECT 要好。
雖然如此，但我們不該完全舍棄 EXCEPT / INTERSECT 的使用，因?yàn)樗麄兊恼Z(yǔ)法簡(jiǎn)潔、容易閱讀。因此在您的 SQL 語(yǔ)句中，除非兩種寫(xiě)法的性能差距很大，不然我仍建議使用 EXCEPT / INTERSECT 寫(xiě)法，以便項(xiàng)目后續(xù)的維護(hù)。若論 EXISTS 和 EXCEPT / INTERSECT 哪一種寫(xiě)法較好，則屬見(jiàn)仁見(jiàn)智的問(wèn)題，端視您的數(shù)據(jù)量、執(zhí)行環(huán)境、實(shí)測(cè)兩種寫(xiě)法的效率差距，來(lái)決定要用哪一種寫(xiě)法。
參考文檔：
[1] EXISTS (Transact-SQL)
http://msdn.microsoft.com/zh-cn/library/ms188336.aspx
[2] SQL 中 IN 和 EXISTS 用法的區(qū)別？
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 語(yǔ)句一起使用 UNION、EXCEPT 和 INTERSECT
http://msdn.microsoft.com/zh-cn/library/ms191523.aspx
[6] 使用 EXCEPT 和 INTERSECT 執(zhí)行半聯(lián)接操作
http://msdn.microsoft.com/zh-cn/library/ms191255.aspx
參考書(shū)籍：
[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
相關(guān)文章：
[8] 探討 .NET 4 新增的 SortedSet 類(lèi) (.NET 平臺(tái)的 EXCEPT、INTERSECT)
http://www.rzrgm.cn/WizardWu/archive/2010/06/17/1759297.html
浙公網(wǎng)安備 33010602011771號(hào)