巢狀查詢是指一個查詢語句巢狀在另一個查詢語句內部的查詢。巢狀查詢也就子查詢,在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不需要執行一行一行的完全連線,而是直接尋找記錄,直到找到第一個匹配的記錄。