Sql Server系列:巢狀查詢

libingql發表於2014-12-01

  巢狀查詢是指一個查詢語句巢狀在另一個查詢語句內部的查詢。巢狀查詢也就子查詢,在SELECT子句中先計運算元查詢,子查詢結果作為外層另一個查詢的過濾條件,查詢可以基於一個表或多個表。子查詢中可以使用比較運算子,如“<”、“<=”、“>”、“>=”等。

  子查詢中常用的操作符有ANY(SOME)、ALL、EXISTS。子查詢可以新增到SELECT 、UPDATE和DELETE語句中,可以進行多層巢狀。

1 使用比較運算子

  子查詢使用比較運算子,如“<”、“<=”、“>”、“>=”等。

  示例:返回單個值的SELECT語句的巢狀查詢

SELECT * FROM [dbo].[Product]
WHERE [UnitPrice] = (
    SELECT MIN([UnitPrice]) FROM [dbo].[Product]
)
SELECT * FROM [dbo].[Product]
WHERE [CategoryID] = 
( SELECT [CategoryID] FROM [dbo].[Category] WHERE [CategoryName] = 'LINQ to SQL' )

2. 使用IN關鍵字

  IN關鍵字進行子查詢時,內層查詢語句僅僅返回一個資料列,這個資料列裡的值將提供給外層查詢語句進行比較。

  示例:返回多個值的子查詢的巢狀查詢

SELECT * FROM [dbo].[Product]
WHERE [CategoryID] IN (
    SELECT [CategoryID] FROM [dbo].[Category] WHERE [CategoryID] <= 10
)

  儘管使用IN的巢狀查詢方式可以實現,但更好的方式是使用內連線實現這種查詢,而不是使用使用巢狀的SELECT。

  上例的子查詢使用INNER JOIN方式:

SELECT [dbo].[Product].* FROM [dbo].[Product]
INNER JOIN [dbo].[Category]
    ON [dbo].[Product].[CategoryID] = [dbo].[Category].[CategoryID]
WHERE [dbo].[Category].[CategoryID] <= 10

  出於效能方面的考慮,如果沒有特別的理由要使用巢狀的SELECT,則還是應使用連線方式作為預設的解決方案。在大部分情況下,SQL Server會將巢狀子查詢解決方案解析為和使用連線用於的查詢計劃,在檢查巢狀子查詢和內連線查詢計劃時,會發現它們的完全相同的計劃。大多數情況下,這兩種方法沒有多大的區別。當查詢計劃不同時,連線通常的更好的選擇。

  SELECT語句中可以使用NOT IN運算子,其作用與IN相反。

3. ANY、SOME關鍵字

  ANY和SOME關鍵字是同義詞,表示滿足其中任一條街。它們允許建立一個表示式對子查詢的返回值列表進行比較,只要滿足內層子查詢中任何一個比較條件,就返回一個結果作為外層查詢的條件。

  ANY關鍵字接在一個比較操作符的後面,表示與子查詢返回的任何值比較為TRUE,則返回TRUE。

  示例:

IF 20 > ANY ( SELECT [UnitsInStock] FROM [dbo].[Product] )
    PRINT '1'
ELSE
    PRINT '0'

4. ALL關鍵字

  ALL關鍵字用於需要同時滿足所有內層查詢的條件,只有當子查詢返回的所有值比較都為TRUE,才返回TRUE。

  示例:

IF 20 > ALL ( SELECT [UnitsInStock] FROM [dbo].[Product] )
    PRINT '1'
ELSE
    PRINT '0'

5. EXISTS關鍵字

  EXISTS關鍵字後面的引數是一個任意的子查詢,系統對子查詢進行運算以判斷它是否返回行,如果至少返回一行,則EXISTS的結果為TRUE,此時外層查詢語句將進行查詢;如果子查詢沒有返回任何行,那麼EXISTS返回的結果是FALSE,此時外層語句將不進行查詢。

  示例:

SELECT * FROM [dbo].[Category]
WHERE EXISTS (
    SELECT * FROM [dbo].[Product]
    WHERE [CategoryID] = 1
)

  EXISTS關鍵字還可以和條件表示式一起使用。

SELECT * FROM [dbo].[Category]
WHERE [CategoryName] ='LINQ to SQL'
AND EXISTS (
    SELECT * FROM [dbo].[Product]
    WHERE [CategoryID] = 1
)

  NOT EXISTS與EXISTS使用方法相同,返回的結果相反。

SELECT * FROM [dbo].[Category]
WHERE NOT EXISTS (
    SELECT * FROM [dbo].[Product]
    WHERE [CategoryID] = 1
)

  Exists關聯子查詢:查詢所有存在Product的Category

SELECT * FROM [dbo].[Category] c
WHERE EXISTS (
    SELECT * FROM [dbo].[Product] p
    WHERE p.[CategoryID] = c.[CategoryID]
)

  上例同樣可以使用INNER JOIN實現:

SELECT c.* FROM [dbo].[Category] c
INNER JOIN [dbo].[Product] p
    ON p.[CategoryID] = c.[CategoryID]

  基於連線的語法可以得到相同的結果,但使用EXISTS具有更好的效能。使用EXISTS關鍵字,SQL Server不需要執行一行一行的完全連線,而是直接尋找記錄,直到找到第一個匹配的記錄。

相關文章