T-SQL查詢進階--深入了解子查詢

引言

      SQL有着非常強大且靈活的查詢方式，而多表連接配接操作往往也可以用子查詢進行替代,本篇文章将會講述子查詢的方方面面。

簡介

      子查詢本質上是嵌套進其他SELECT,UPDATE,INSERT,DELETE語句的一個被限制的SELECT語句,在子查詢中，隻有下面幾個子句可以使用

SELECT子句（必須）

FROM子句(必選）

WHERE子句(可選)

GROUP BY(可選)

HAVING(可選)

ORDER BY(隻有在TOP關鍵字被使用時才可用)

    子查詢也可以嵌套在其他子查詢中,這個嵌套最多可達32層。子查詢也叫内部查詢(Inner query)或者内部選擇(Inner Select),而包含子查詢的查詢語句也叫做外部查詢（Outter)或者外部選擇(Outer Select),子查詢的概念可以簡單用下圖闡述:

1

     上圖是作為資料源使用的一個子查詢.

     通常來講，子查詢按照子查詢所傳回資料的類型，可以分為三種，分别為：

傳回一張資料表(Table)

傳回一列值(Column)

傳回單個值(Scalar)

     下面，我們按照這三種方式來闡述子查詢

子查詢作為資料源使用

     當子查詢在外部查詢的FROM子句之後使用時,子查詢被當作一個資料源使用,即使這時子查詢隻傳回一個單一值(Scalar)或是一列值(Column)，在這裡依然可以看作一個特殊的資料源,即一個二維資料表(Table).作為資料源使用的子查詢很像一個View(視圖),隻是這個子查詢隻是臨時存在，并不包含在資料庫中。

     比如這個語句:

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

    上述子查詢語句将ProductModel表中的子集M,作為資料源（表）和Product表進行内連接配接。結果如下:

   2

    作為資料源使用也是子查詢最簡單的應用。當然，當子查詢作為資料源使用時，也分為相關子查詢和無關子查詢，這會在文章後面介紹到.

子查詢作為選擇條件使用

    作為選擇條件的子查詢也是子查詢相對最複雜的應用.

    作為選擇條件的子查詢是那些隻傳回一列(Column)的子查詢，如果作為選擇條件使用，即使隻傳回單個值，也可以看作是隻有一行的一列.比如:

    在AdventureWorks中：

    我想取得總共請病假天數大于68小時的員工:

SELECT [FirstName]

      ,[MiddleName]

      ,[LastName]

  FROM [AdventureWorks].[Person].[Contact]

  WHERE ContactID IN 

  (SELECT EmployeeID

  FROM [AdventureWorks].[HumanResources].[Employee]

  WHERE SickLeaveHours>68)

   結果如下：

   3

   上面的查詢中，在IN關鍵字後面的子查詢傳回一列值作為外部查詢的選擇條件使用.

   同樣的，與IN關鍵字的邏輯取反的NOT IN關鍵字，這裡就不再闡述了

   但是要強調的是，不要用IN和NOT IN關鍵字，這會引起很多潛在的問題，這篇文章對這個問題有着很好的闡述:http://wiki.lessthandot.com/index.php/Subquery_typo_with_using_in。這篇文章的觀點是永遠不要再用IN和NOT IN關鍵字，我的觀點是存在即合理，我認為隻有在IN裡面是固定值的時候才可以用IN和NOT IN，比如:

  WHERE ContactID  IN (25,33)

   隻有在上面這種情況下，使用IN和NOT IN關鍵字才是安全的，其他情況下，最好使用EXISTS,NOT EXISTS,JOIN關鍵字來進行替代. 除了IN之外，用于選擇條件的關鍵字還有ANY和ALL,這兩個關鍵字和其字面意思一樣. 和"<",">",”="連接配接使用，比如上面用IN的那個子查詢：

   我想取得總共請病假天數大于68小時的員工

   用ANY關鍵字進行等效的查詢為:

  WHERE ContactID =ANY

   在作為ANY和ALL關鍵字在子查詢中使用時，所實作的效果如下

=ANY 和IN等價

<>ALL 和NOT IN等價

>ANY 大于最小的(>MIN)

<ANY 小于最大的(<MAX)

>ALL 大于最大的(>MAX)

<ALL 小于最小的(<MIN)

=ALL 下面說

   =ALL關鍵字很少使用，這個的效果在子查詢中為如果隻有一個傳回值，則和“=”相等，而如果有多個傳回值，結果為空

   這裡要注意，SQL是一種很靈活的語言，就像子查詢所實作的效果可以使用JOIN來實作一樣（效果一樣，實作思路不同），ANY和ALL所實作的效果也完全可以使用其他方式來替代,按照上面表格所示，>ANY和>MIN完全等價，比如下面兩個查詢語句完全等價:

SELECT *

FROM AdventureWorks.HumanResources.Employee

WHERE SickLeaveHours>ANY

(SELECT SickLeaveHours FROM AdventureWorks.HumanResources.Employee WHERE SickLeaveHours>68)

WHERE SickLeaveHours>

(SELECT MIN(SickLeaveHours) FROM AdventureWorks.HumanResources.Employee WHERE SickLeaveHours>68)

相關子查詢和EXISTS關鍵字

   前面所說的查詢都是無關子查詢(Uncorrelated subquery),子查詢中還有一類很重要的查詢是相關子查詢(Correlated subquery),也叫重複子查詢比如，還是上面那個查詢，用相關子查詢來寫:

   我想取得總共請病假天數大于68天的員工:

  FROM [AdventureWorks].[Person].[Contact] c

  WHERE EXISTS

  (SELECT *

  FROM [AdventureWorks].[HumanResources].[Employee] e

  WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)

   結果和使用IN關鍵字的查詢結果相同:

   如何差別相關子查詢和無關子查詢呢?最簡單的辦法就是直接看子查詢本身能否執行，比如上面的例子中的子查詢：

(SELECT *

   這一句本身執行本身會報錯.因為這句引用到了外部查詢的表

   對于無關子查詢來說，整個查詢的過程為子查詢隻執行一次，然後交給外部查詢，比如：

SQLRESULT

   上面的無關子查詢，整個查詢過程可以看作是子查詢首先傳回SQLResult(SQL結果集),然後交給外部查詢使用，整個過程子查詢隻執行一次

    而相反,作為相關子查詢,子查詢的執行的次數依賴于外部查詢，外部查詢每執行一行，子查詢執行一次，比如：

    還是上面的例子:我想取得總共請病假天數大于68天的員工

----

step 1:

  WHERE 1=e.ContactID AND e.SickLeaveHours>68)

step 2:

  WHERE 2=e.ContactID AND e.SickLeaveHours>68)

step n:

  WHERE n=e.ContactID AND e.SickLeaveHours>68)

  如上面代碼所示。上面的相關子查詢實際上會執行N次（N取決與外部查詢的行數），外部查詢每執行一行，都會将對應行所用的參數傳到子查詢中，如果子查詢有對應值，則傳回TRUE（既目前行被選中并在結果中顯示），如果沒有，則傳回FALSE。然後重複執行下一行。

子查詢作為計算列使用

    當子查詢作為計算列使用時，隻傳回單個值(Scalar) 。用在SELECT語句之後，作為計算列使用。同樣分為相關子查詢和無關子查詢

    相關子查詢的例子比如：我想取得每件産品的名稱和總共的銷量

SELECT [Name],

      (SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderDetail S

      WHERE S.ProductID=P.ProductID) AS SalesAmount

FROM [AdventureWorks].[Production].[Product] P

   部分結果如下：

   5

   當子查詢作為計算列使用時，會針對外部查詢的每一行，傳回唯一的值。

   同樣的，SQL子查詢都可以使用其他語句達到同樣的效果，上面的語句和如下語句達到同樣的效果：

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

   子查詢作為計算列且作為無關子查詢時使用，隻會一次性傳回但一值，這裡就不再闡述了。

小結

     本篇文章通過子查詢的三種不同用途來闡述子查詢。同時，所有的子查詢還可以分為相關子查詢和無關子查詢，而子查詢所實作的功能都可以使用連接配接或者其他方式實作。但一個好的作家應該是掌握豐富的詞彙，而不是僅僅能表達出自己的意思。學會多種SQL查詢方式是學習SQL查詢必經之路。

分類: T-SQL

本文轉自CareySon部落格園部落格，原文連結http://www.cnblogs.com/CareySon/archive/2011/07/18/2109406.html，如需轉載請自行聯系原作者