T-SQL查詢進(jìn)階之深入理解子查詢
子查詢本質(zhì)上是嵌套進(jìn)其他SELECT,UPDATE,INSERT,DELETE語(yǔ)句的一個(gè)被限制的SELECT語(yǔ)句,在子查詢中，只有下面幾個(gè)子句可以使用：SELECT子句（必須）、FROM子句(必選）、 WHERE子句(可選) 、GROUP BY(可選) 、HAVING(可選) 和ORDER BY(只有在TOP關(guān)鍵字被使用時(shí)才可用)。
子查詢也可以嵌套在其他子查詢中,這個(gè)嵌套最多可達(dá)32層。子查詢也叫內(nèi)部查詢(Inner query)或者內(nèi)部選擇(Inner Select),而包含子查詢的查詢語(yǔ)句也叫做外部查詢（Outter)或者外部選擇(Outer Select),子查詢的概念可以簡(jiǎn)單用下圖闡述:
http://wiki.lessthandot.com/index.php/Subquery_typo_with_using_in。這篇文章的觀點(diǎn)是永遠(yuǎn)不要再用IN和NOT IN關(guān)鍵字，我的觀點(diǎn)是存在即合理，我認(rèn)為只有在IN里面是固定值的時(shí)候才可以用IN和NOT IN，比如:
上圖是作為數(shù)據(jù)源使用的一個(gè)子查詢。
通常來(lái)講，子查詢按照子查詢所返回?cái)?shù)據(jù)的類型，可以分為三種，分別為：
返回一張數(shù)據(jù)表(Table)
返回一列值(Column)
返回單個(gè)值(Scalar)
下面，我們按照這三種方式來(lái)闡述子查詢
子查詢作為數(shù)據(jù)源使用
當(dāng)子查詢?cè)谕獠坎樵兊腇ROM子句之后使用時(shí),子查詢被當(dāng)作一個(gè)數(shù)據(jù)源使用,即使這時(shí)子查詢只返回一個(gè)單一值(Scalar)或是一列值(Column)，在這里依然可以看作一個(gè)特殊的數(shù)據(jù)源,即一個(gè)二維數(shù)據(jù)表(Table).作為數(shù)據(jù)源使用的子查詢很像一個(gè)View(視圖),只是這個(gè)子查詢只是臨時(shí)存在，并不包含在數(shù)據(jù)庫(kù)中。
比如這個(gè)語(yǔ)句：
- SELECT P.ProductID, P.Name, P.ProductNumber, M.Name
- AS ProductModelName
- FROM Production.Product AS P
- INNER JOIN
- (
- SELECT Name, ProductModelID
- FROM Production.ProductModel)
- AS M
- ON P.ProductModelID = M.ProductModelID
上述子查詢語(yǔ)句將ProductModel表中的子集M,作為數(shù)據(jù)源（表）和Product表進(jìn)行內(nèi)連接。結(jié)果如下：
作為數(shù)據(jù)源使用也是子查詢最簡(jiǎn)單的應(yīng)用。當(dāng)然，當(dāng)子查詢作為數(shù)據(jù)源使用時(shí)，也分為相關(guān)子查詢和無(wú)關(guān)子查詢，這會(huì)在文章后面介紹到.
子查詢作為選擇條件使用
作為選擇條件的子查詢也是子查詢相對(duì)最復(fù)雜的應(yīng)用.
作為選擇條件的子查詢是那些只返回一列(Column)的子查詢，如果作為選擇條件使用，即使只返回單個(gè)值，也可以看作是只有一行的一列.比如，在AdventureWorks中：我想取得總共請(qǐng)病假天數(shù)大于68小時(shí)的員工：
- SELECT [FirstName]
- ,[MiddleName]
- ,[LastName]
- FROM [AdventureWorks].[Person].[Contact]
- WHERE ContactID IN
- (SELECT EmployeeID
- FROM [AdventureWorks].[HumanResources].[Employee]
- WHERE SickLeaveHours>68)
結(jié)果如下：
上面的查詢中，在IN關(guān)鍵字后面的子查詢返回一列值作為外部查詢的選擇條件使用。
同樣的，與IN關(guān)鍵字的邏輯取反的NOT IN關(guān)鍵字，這里就不再闡述了。
但是要強(qiáng)調(diào)的是，不要用IN和NOT IN關(guān)鍵字，這會(huì)引起很多潛在的問(wèn)題，這篇文章對(duì)這個(gè)問(wèn)題有著很好的闡述：
- SELECT [FirstName]
- ,[MiddleName]
- ,[LastName]
- FROM [AdventureWorks].[Person].[Contact]
- WHERE ContactID IN (25,33)
只有在上面這種情況下，使用IN和NOT IN關(guān)鍵字才是安全的，其他情況下，最好使用EXISTS,NOT EXISTS,JOIN關(guān)鍵字來(lái)進(jìn)行替代. 除了IN之外，用于選擇條件的關(guān)鍵字還有ANY和ALL,這兩個(gè)關(guān)鍵字和其字面意思一樣. 和"<",">",”="連接使用，比如上面用IN的那個(gè)子查詢：
我想取得總共請(qǐng)病假天數(shù)大于68小時(shí)的員工
用ANY關(guān)鍵字進(jìn)行等效的查詢?yōu)椋?/p>
- SELECT [FirstName]
- ,[MiddleName]
- ,[LastName]
- FROM [AdventureWorks].[Person].[Contact]
- WHERE ContactID =ANY
- SELECT EmployeeID
- FROM [AdventureWorks].[HumanResources].[Employee]
- WHERE SickLeaveHours>68)
在作為ANY和ALL關(guān)鍵字在子查詢中使用時(shí)，所實(shí)現(xiàn)的效果如下：
| =ANY | 和IN等價(jià) |
| <>ANY | 和NOT IN等價(jià) |
| >ANY | 大于最小的(>MIN) |
| <ANY | 小于最大的(<MAX) |
| >ALL | 大于最大的(>MAX) |
| <ALL | 小于最小的(<MIN) |
| =ALL | 下面說(shuō) |
=ALL關(guān)鍵字很少使用，這個(gè)的效果在子查詢中為如果只有一個(gè)返回值，則和“=”相等，而如果有多個(gè)返回值，結(jié)果為空。
這里要注意，SQL是一種很靈活的語(yǔ)言，就像子查詢所實(shí)現(xiàn)的效果可以使用JOIN來(lái)實(shí)現(xiàn)一樣（效果一樣，實(shí)現(xiàn)思路不同），ANY和ALL所實(shí)現(xiàn)的效果也完全可以使用其他方式來(lái)替代,按照上面表格所示，>ANY和>MIN完全等價(jià)，比如下面兩個(gè)查詢語(yǔ)句完全等價(jià)：
- SELECT *
- FROM AdventureWorks.HumanResources.Employee
- WHERE SickLeaveHours>ANY
- (SELECT SickLeaveHours
- FROM AdventureWorks.HumanResources.Employee
- WHERE SickLeaveHours>68)
- SELECT *
- FROM AdventureWorks.HumanResources.Employee
- WHERE SickLeaveHours>
- (SELECT MIN(SickLeaveHours)
- FROM AdventureWorks.HumanResources.Employee
- WHERE SickLeaveHours>68)
#p#
相關(guān)子查詢和EXISTS關(guān)鍵字
前面所說(shuō)的查詢都是無(wú)關(guān)子查詢(Uncorrelated subquery),子查詢中還有一類很重要的查詢是相關(guān)子查詢(Correlated subquery),也叫重復(fù)子查詢比如，還是上面那個(gè)查詢，用相關(guān)子查詢來(lái)寫(xiě):
我想取得總共請(qǐng)病假天數(shù)大于68天的員工:
- SELECT [FirstName]
- ,[MiddleName]
- ,[LastName]
- FROM [AdventureWorks].[Person].[Contact] c
- WHERE EXISTS
- (SELECT *
- FROM [AdventureWorks].[HumanResources].[Employee] e
- WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)
結(jié)果和使用IN關(guān)鍵字的查詢結(jié)果相同：
如何區(qū)別相關(guān)子查詢和無(wú)關(guān)子查詢呢?最簡(jiǎn)單的辦法就是直接看子查詢本身能否執(zhí)行，比如上面的例子中的子查詢：
- (SELECT *
- FROM [AdventureWorks].[HumanResources].[Employee] e
- WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)
這一句本身執(zhí)行本身會(huì)報(bào)錯(cuò).因?yàn)檫@句引用到了外部查詢的表。
對(duì)于無(wú)關(guān)子查詢來(lái)說(shuō)，整個(gè)查詢的過(guò)程為子查詢只執(zhí)行一次，然后交給外部查詢，比如：
- SELECT *
- FROM AdventureWorks.HumanResources.Employee
- WHERE SickLeaveHours>ANY
- SQLRESULT
上面的無(wú)關(guān)子查詢，整個(gè)查詢過(guò)程可以看作是子查詢首先返回SQLResult(SQL結(jié)果集),然后交給外部查詢使用，整個(gè)過(guò)程子查詢只執(zhí)行一次。
而相反,作為相關(guān)子查詢,子查詢的執(zhí)行的次數(shù)依賴于外部查詢，外部查詢每執(zhí)行一行，子查詢執(zhí)行一次，比如：
還是上面的例子:我想取得總共請(qǐng)病假天數(shù)大于68天的員工：
- SELECT [FirstName]
- ,[MiddleName]
- ,[LastName]
- FROM [AdventureWorks].[Person].[Contact] c
- WHERE EXISTS
- (SELECT *
- FROM [AdventureWorks].[HumanResources].[Employee] e
- WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)
step 1:
- SELECT [FirstName]
- ,[MiddleName]
- ,[LastName]
- FROM [AdventureWorks].[Person].[Contact] c
- WHERE EXISTS
- (SELECT *
- FROM [AdventureWorks].[HumanResources].[Employee] e
- WHERE 1=e.ContactID AND e.SickLeaveHours>68)
step 2:
- SELECT [FirstName]
- ,[MiddleName]
- ,[LastName]
- FROM [AdventureWorks].[Person].[Contact] c
- WHERE EXISTS
- (SELECT *
- FROM [AdventureWorks].[HumanResources].[Employee] e/p>
- WHERE 2=e.ContactID AND e.SickLeaveHours>68)
step n:
- SELECT [FirstName]
- ,[MiddleName]
- ,[LastName]
- FROM [AdventureWorks].[Person].[Contact] c
- WHERE EXISTS
- (SELECT *
- FROM [AdventureWorks].[HumanResources].[Employee] e
- WHERE n=e.ContactID AND e.SickLeaveHours>68)
如上面代碼所示。上面的相關(guān)子查詢實(shí)際上會(huì)執(zhí)行N次（N取決與外部查詢的行數(shù)），外部查詢每執(zhí)行一行，都會(huì)將對(duì)應(yīng)行所用的參數(shù)傳到子查詢中，如果子查詢有對(duì)應(yīng)值，則返回TRUE（既當(dāng)前行被選中并在結(jié)果中顯示），如果沒(méi)有，則返回FALSE。然后重復(fù)執(zhí)行下一行。
子查詢作為計(jì)算列使用
當(dāng)子查詢作為計(jì)算列使用時(shí)，只返回單個(gè)值(Scalar) 。用在SELECT語(yǔ)句之后，作為計(jì)算列使用。同樣分為相關(guān)子查詢和無(wú)關(guān)子查詢
相關(guān)子查詢的例子比如：我想取得每件產(chǎn)品的名稱和總共的銷(xiāo)量。
- SELECT [Name],
- (SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderDetail S
- WHERE S.ProductID=P.ProductID)AS SalesAmount
- FROM [AdventureWorks].[Production].[Product] P
部分結(jié)果如下：
當(dāng)子查詢作為計(jì)算列使用時(shí)，會(huì)針對(duì)外部查詢的每一行，返回唯一的值。
同樣的，SQL子查詢都可以使用其他語(yǔ)句達(dá)到同樣的效果，上面的語(yǔ)句和如下語(yǔ)句達(dá)到同樣的效果：
- SELECT P.Name,COUNT(S.ProductID)
- FROM [AdventureWorks].[Production].[Product] P
- LEFT JOIN AdventureWorks.Sales.SalesOrderDetail S
- ON S.ProductID=P.ProductID
- GROUP BY P.Name
子查詢作為計(jì)算列且作為無(wú)關(guān)子查詢時(shí)使用，只會(huì)一次性返回但一值，這里就不再闡述了。
【編輯推薦】
