T-SQL進階：超越基礎 Level 2：編寫子查詢

By Gregory Larsen, 2016/01/01 (首次釋出於: 2014/01/29)

關於系列

本文屬於進階系列：T-SQL進階：超越基礎

跟隨Gregory Larsen的T-SQL DML進階系列，其涵蓋了更多的高階方面的T-SQL語言，如子查詢。

在您開始建立超出基本Transact-SQL語句的更復雜的SQL程式碼時，您可能會發現需要使用其他SELECT語句的結果來限制查詢。 當在父Transact-SQL語句中嵌入SELECT語句時，這些嵌入式SELECT語句被稱為子查詢或相關子查詢。 在“超越基礎”樓梯的這個層次上，我將討論一個子查詢的不同方面，在將來的一個層面上，我將討論相關的子查詢。

什麼是子查詢？

子查詢只是一個SELECT語句，它包含在另一個Transact-SQL語句中。可以在任何可以使用表示式的地方使用子查詢。許多子查詢返回單個列值，因為它們與比較運算子（=，！=，<，<=，>，> =）或表示式結合使用。當子查詢不用作表示式或使用比較運算子時，它可以返回多個值。此外，子查詢甚至可以在FROM子句或關鍵字EXISTS中使用時返回多個列和值。

子查詢容易在Transact-SQL語句中發現，因為它將是括號中的SELECT語句。由於子查詢包含在Transact-SQL語句中，因此子查詢通常稱為內部查詢。而包含子查詢的Transact-SQL語句被稱為外部查詢。子查詢的另一個特點是可以獨立於外部查詢執行，並且將無錯誤地執行，並且可能返回一組行或空行集。

子查詢的另一種形式是相關子查詢。但是相關的子查詢不能獨立於外部的Transact SQL語句執行。相關子查詢使用外部查詢中的列或列來約束從相關子查詢返回的結果。這對於本文的相關子查詢足夠了。我將在未來的樓梯文章中探索相關的子查詢。

使用子查詢時還需要考慮以下幾點：

• ntext，text和image資料型別不允許從子查詢返回
• ORDER BY子句不能用於子查詢，除非使用TOP操作符
• 使用子查詢的檢視無法更新
• COMPUTE和INTO子句不能在子查詢中使用

子查詢示例資料示例

為了演示如何使用子查詢，我將需要一些測試資料。 而不是建立自己的測試資料，我的所有示例都將使用AdventureWorks2008R2資料庫。 如果您想跟隨並在環境中執行我的示例，那麼您可以從這裡下載AdventureWorks2008R2資料庫：http://msftdbprodsamples.code…

返回單個值的子查詢的示例

如上所述，在表示式中使用的子查詢或返回比較運算子一側的值需要返回單個值。 Transact-SQL語句中有許多不同的地方，需要一個子查詢來返回單個列值，例如在選擇列表中WHERE子句等。在本節中，我將提供一系列示例，演示如何使用子查詢 作為表示式或與比較運算子以滿足不同的業務需求。

列列表中的子查詢

列列表中的子查詢是SELECT語句，它返回放置在SELECT子句的列列表中的單個列值。 為了演示如何在選擇列表中使用子查詢，我們假設我們必須從具有以下業務需求的SELECT語句生成一個結果集：

• 返回所有Sales.SalesOrderHeader記錄有什麼有OrderDate等於“2007-02-19 00：00：00.000”
• 通過SalesOrderID命令返回的記錄
• 編號每行返回的最舊的順序的RowNumber為1，next oldest的RowNumber為2等
• 結果集需要一個名為TotalOrders的列，需要使用等於“2007-02-19 00：00：00.000”的OrderDate的總訂單數量進行填充

清單1中列出了滿足這些要求的程式碼。

SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) RowNumber
      , (SELECT COUNT(*) 
         FROM [Sales].[SalesOrderHeader] 
         WHERE ModifiedDate = `2007-02-19 00:00:00.000`) 
                     AS TotalOrders
      , *
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = `2007-02-19 00:00:00.000`;

清單1：列列表中的子查詢

在這個單一的Transact-SQL語句中，您會看到兩個不同的SELECT子句。 子查詢是嵌入在清單1中的語句中間的SELECT語句，它在它周圍有括號。 我已經刪除了子查詢語句，並將其放在清單2中，以防您想要測試以驗證它可以獨立於完整的Transact-SQL語句執行。

SELECT COUNT(*) 
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = `2007-02-19 00:00:00.000`

清單2：清單1中的子查詢語句

通過將此子查詢列在列列表中，清單1中的此Transact-SQL語句可以對OrderDate為“2007-02-19 00：00：00.000”的SalesOrderHeader行的數量進行計數，並將該資訊與詳細資訊一起返回 有關具有相同OrderDate值的Sales.SalesOrderHeader記錄的行資訊。

WHERE子句中子查詢的示例

有時你想根據SELECT語句的結果來驅動WHERE子句條件。 當您在WHERE子句中的SELECT語句時，此SELECT語句實際上是一個子查詢。 要演示在WHERE子句中使用子查詢，假設您需要顯示包含購買超大型長袖徽標運動衫的Sales.SalesOrderDetail記錄。 清單3中的程式碼通過使用子查詢來滿足我的顯示要求。

SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = (SELECT ProductID 
                   FROM [Production].[Product]
                    WHERE Name = `Long-Sleeve Logo Jersey, XL`); 
                    

清單3：WHERE子句中的子查詢

清單3中的子查詢位於WHERE條件的右側。 此子查詢標識Product.Product記錄的ProductID，其中產品名稱為“Long-Sleeve Logo Jersey，XL”。 此子查詢允許我找到具有與“Long-Sleeve Logo Jersey，XL”的產品名稱相關聯的ProductID的所有Sales.SalesOrderDetail記錄。

使用子查詢來控制TOP條款的示例

使用TOP子句返回的行數可以由表示式控制。 清單5中的程式碼標識了應該根據TOP子句中的子查詢返回的Sales.SalesOrderDetail行的數量。

SELECT TOP (SELECT TOP 1 OrderQty 
            FROM [Sales].[SalesOrderDetail]
            ORDER BY ModifiedDate) *  
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 716;

清單4：TOP子句中的子查詢

清單4中的程式碼使用從子查詢返回的OrderQty值來標識將在TOP子句中使用的值。 通過使用子查詢來控制TOP子句返回的行數，可以構建一個子查詢，以便在執行時動態地識別從查詢返回的行數。

子條款示例

為了演示在HAVING子句中使用子查詢，假設您具有以下業務要求：

生成包含Sales.SalesOrderHeader.OrderDate和每個日期的訂單數量的結果集，其中訂單數量超過“2006-05-01”上執行的訂單數量。

為了滿足這個要求，我開發了清單6中使用HAVING子句中的子查詢的查詢。

SELECT count(*), OrderDate 
FROM [Sales].[SalesOrderHeader]
GROUP BY OrderDate
HAVING count(*) >
       (SELECT count(*) 
        FROM [Sales].[SalesOrderHeader]
        WHERE OrderDate = `2006-05-01 00:00:00.000`);
        

清單5：HAVING子句中的子查詢

清單5中的程式碼具有HAVING子句右側的子查詢，並在我的子查詢中使用COUNT函式來確定“2006-05-01”上的訂單數量。

在函式呼叫中使用子查詢的示例

要演示在函式呼叫中使用子查詢，假設您需要顯示OrderDate和每個Sales.SalesOrderHeader記錄的最大OrderDate之間的天數。 清單6中的程式碼符合此要求。

SELECT SalesOrderID
      , OrderDate
      ,DATEDIFF
          (
            dd,OrderDate
        ,(SELECT MAX(OrderDate)
          FROM [Sales].[SalesOrderHeader])
          ) AS DaysBetweenOrders
         ,(SELECT MAX(OrderDate)
        FROM [Sales].[SalesOrderHeader]) 
            AS MaxOrderDate
FROM [Sales].[SalesOrderHeader];

清單6：函式呼叫中的子查詢

清單6中的程式碼有兩個不同的子查詢。 兩個子查詢返回Sales.SalesOrderHeader表中的最大OrderDate。 但是第一個子查詢用於將日期傳遞給DATEDIFF函式的第二個引數。

返回多個值的子查詢的示例

我迄今為止的所有示例都包含僅在單個列中返回單個值的子查詢。 並不是所有的子查詢都有這個要求。 接下來的幾個例子將使用返回多個值和/或多個列的子查詢。

FROM子句中的子查詢示例

在FROM子句中，通常會標識您的Transact-SQL語句將對其執行的表或表的集合。 每個表提供一組記錄，您的查詢將用於確定查詢的最終結果集。 子查詢可以被認為是返回一組記錄的查詢，因此它可以像FROM表一樣在FROM子句中使用。 清單7中的查詢顯示了我如何在FROM子句中使用子查詢。 當在FROM子句中使用子查詢時，從子查詢生成的結果集通常稱為派生表。

SELECT SalesOrderID 
FROM (SELECT TOP 10 SalesOrderID 
      FROM [Sales].[SalesOrderDetail]
      WHERE ProductID = 716
      ORDER BY ModifiedDate DESC) AS Last10SalesOrders;
      

清單7：FROM子句中的子查詢

• 清單7中的程式碼使用FROM子句中的子查詢來建立一個名為Last10SalesOrders的表別名。 我的子查詢返回包含ProductID為716的最後10個Sales.alesOrderDetail記錄。

• 清單7中的程式碼是一個非常簡單的例子，說明如何在FROM子句中使用子查詢。 通過在FROM子句中使用子查詢，您可以輕鬆地構建更復雜的FROM語法，該語法將子查詢的結果與其他表或其他子查詢相結合，如清單8所示。

  
  
  SELECT DISTINCT OrderDate
  FROM (SELECT TOP 10 SalesOrderID 
        FROM [Sales].[SalesOrderDetail]
        WHERE ProductID = 716
        ORDER BY ModifiedDate DESC) AS Last10SalesOrders
  JOIN [Sales].[SalesOrderHeader] AS SalesOrderHeader
  ON Last10SalesOrders.SalesOrderID = SalesOrderHeader.SalesOrderID
  ORDER BY OrderDate
  

清單8：使用實際表連線派生表

在清單8中，我看到了我在清單7中建立的子查詢/派生表，並將其與SalesOrderHeader表相加。 通過這樣做，我可以確定最後10次訂購ProductID = 716的OrderDate。

使用具有IN關鍵字的子查詢的示例

您可以編寫一個返回列的多個值的子查詢的地方是當您的子查詢生成與IN關鍵字一起使用的記錄集時。 清單9中的程式碼演示瞭如何使用子查詢將值傳遞給IN關鍵字。

SELECT * FROM [Sales].[SalesOrderDetail] 
WHERE ProductID IN 
        (SELECT ProductID 
         FROM [Production].[Product]
         WHERE Name like `%XL%`);
         

清單9：使用子查詢將值傳遞給IN關鍵字

清單9中的程式碼使用一個子查詢從Product.Product表中返回不同的ProductID值，其名稱包含字元“XL”。 然後在IN關鍵字中使用從子查詢返回的這些ProductID值來約束從Sales.SalesOrderDetail表返回哪些行。

在修改資料的語句中使用子查詢的示例

到目前為止，我的所有示例一直在演示如何在SELECT語句的不同部分中使用子查詢。 也可以在INSERT，UPDATE或DELETE語句中使用子查詢。 清單10中的程式碼顯示瞭如何在INSERT語句中使用子查詢。

DECLARE @SQTable TABLE (
OrderID int,
OrderDate datetime,
TotalDue money,
MaxOrderDate datetime);

-- INSERT with SubQuery
INSERT INTO @SQTable 
   SELECT SalesOrderID,
          OrderDate, 
          TotalDue, 
          (SELECT MAX(OrderDate) 
           FROM [Sales].[SalesOrderHeader]) 
   FROM [Sales].[SalesOrderHeader]
   WHERE CustomerID = 29614;

-- Display Records
SELECT * FROM @SQtable;

清單10：INSERT語句中的子查詢

在清單10中的程式碼中，我使用一個子查詢來計算要插入列MaxOrderDate的值。 這只是在INSERT語句中如何使用子查詢的一個示例。 請記住，也可以在UPDATE和/或DELETE語句中使用子查詢。

子查詢和JOIN之間的效能考慮

如果您已閱讀由Microsoft生成的“子查詢基礎知識”文件（http://technet.microsoft.com/…），那麼您可能已經在此語句中執行 包含子查詢的語句的效能：

“在Transact-SQL中，包含子查詢的語句和不具有語義相似的版本的語句通常沒有效能差異。

要將使用子查詢的查詢的效能與不使用子查詢的等效查詢進行比較，我將在清單3中重寫我的子查詢以使用JOIN操作。 清單11顯示了我重寫的JOIN查詢，相當於清單3中的查詢。

SELECT SOD.* 
FROM [Sales].[SalesOrderDetail] AS SOD
INNER JOIN 
[Production].[Product] AS P
ON SOD.ProductID = P.ProductID
WHERE P.Name = `Long-Sleeve Logo Jersey, XL`;

清單11：與清單3中的查詢相當的JOIN查詢

要比較使用子查詢的清單3中的查詢的效能和使用JOIN的清單11中的查詢，我將使用清單12中的程式碼執行兩個查詢。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Listing 3 query
SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = (SELECT ProductID 
                   FROM Production.Product
                    WHERE Name = `Long-Sleeve Logo Jersey, XL`); 

-- Listing 11 query
SELECT SOD.* 
FROM [Sales].[SalesOrderDetail] AS SOD
INNER JOIN 
[Production].[Product] AS P
ON SOD.ProductID = P.ProductID
WHERE P.Name = `Long-Sleeve Logo Jersey, XL`;

清單12：測試清單3和清單4的效能程式碼

在執行列表12中的程式碼之後，我回顧了“SET STATISTICS”語句生成的訊息。 通過檢視統計資訊，我發現這兩個查詢對SalesOrderDetail表都有3,309個邏輯讀取，對於Product表有兩個邏輯讀取，每個使用31 ms的CPU。 另外我檢視了SQL Server為這兩個查詢建立的執行計劃。 我發現SQL Server為兩者生成了相同的執行計劃。 因此，對於我的情況使用子查詢或JOIN查詢產生了等效的效能，正如微軟所記錄的那樣。

總結

子查詢是嵌入另一個Transact-SQL語句的SELECT語句。子查詢可以獨立於外部查詢執行，因此有時也稱為獨立查詢。記住，任何時候你有一個子查詢代替一個表示式，或者與比較運算子一起使用，它只能返回一個列和值。通常可以使用JOIN邏輯重寫子查詢。子查詢是幫助您構建更復雜的Transact-SQL語句以滿足業務需求的強大工具。

問題和答案

在本節中，您可以通過回答以下問題來檢視您使用子查詢概念瞭解的內容。

問題1：

完成這個句子“一個子查詢是另一個Transact-SQL語句中的SELECT語句，_____________________”。

• 不能獨立於完整的查詢執行。
• 引用來自外部查詢的列。
• 當獨立於外部查詢執行時，它將返回結果。

問題2：

什麼時候子查詢只需要一個列和值才能返回（選擇所有適用的）？

• 當子查詢用於FROM子句時
• 當IN子句中使用子查詢時
• 當表示式中使用子查詢時
• 當子查詢與比較運算子一起使用時

問題3：

在WHERE子句中使用一個子查詢的Transact-SQL語句總是比不包含子查詢（True或False）的等效查詢執行得慢。

• 對
• 錯

回答：

問題1：

正確的答案是c。子查詢可以獨立於外部查詢執行，並返回結果。它不需要來自外部查詢的任何列，如果它有來自外部查詢的列，它將被稱為相關子查詢。

問題2：

正確的答案是c和d。當用作表示式或在比較操作中時，子查詢需要返回一個列值。當子查詢與IN關鍵字一起使用時，它可以返回列的單個或多個值。如果在FROM子句中使用子查詢，它只能返回一列和一個值，但也可以返回多個列和值。

問題3：

正確答案是錯誤的。 SQL Server優化器非常聰明，很可能為兩個等效查詢計算相同的執行計劃。如果包含子查詢的查詢的執行計劃和沒有子查詢的查詢的執行計劃最終都具有相同的執行計劃，則兩個查詢將具有相同的效能。