比較具有相關子查詢的資料列

12 分鐘

小提示

有關更多詳細信息，請參閱 文本和圖像 選項卡！

相關查詢是參考外層查詢欄位的子查詢，形成依賴關係，使得子查詢對外層查詢處理的每一列執行一次。雖然這聽起來效率不高，但相關查詢對於逐列比較和計算非常有用，因為這些方法難以或無法用其他方式表達。

了解相關子查詢的執行

相關子查詢會參考外部查詢中的一個或多個欄位，在兩者之間建立邏輯依賴關係。與一般子查詢執行一次並回傳固定結果不同，相關子查詢會對外層查詢處理的每一列重複執行一次。

可以把它想像成巢狀循環：對於外層查詢的每一列，資料庫都會用該列的值來評估子查詢。這種行為使得強而有力的逐列比較成為可能，但同時也意味著你必須了解執行模型，才能寫出有效率的查詢。

請考慮這兩個問題的差異：

-- Non-correlated subquery (executes once)
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > (SELECT AVG(ListPrice) FROM SalesLT.Product);

-- Correlated subquery (executes per outer row)
SELECT p1.ProductID, p1.Name, p1.ListPrice
FROM SalesLT.Product AS p1
WHERE p1.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p1.ProductCategoryID  -- References outer query
);

在非相關範例中，子查詢計算所有產品的單一平均價格。這個數值會被計算一次，然後將每個產品的價格與該固定數字做比較。

在相關範例中，子查詢引用自外部查詢的 p1.ProductCategoryID。這會產生依賴關係：對於每一筆產品列，子查詢會計算該特定類別的平均價格。「自行車」類別的產品會與自行車的平均水平做比較，而「配件」類別的產品則與配件平均比對。

備註

查詢優化器通常會將相關的子查詢在內部轉換成等效的連接。然而，理解邏輯相關的行為能幫助你撰寫正確的查詢，即使實際執行方式不同。

帶有相關子查詢的篩選

子句中的 WHERE 相關子查詢可實現行特定的篩選條件，而這是靜態比較無法做到的。每列的評估不是與單一固定值比較，而是根據該列的屬性動態計算值。

此模式在您需要識別群組內的異常值、尋找超出自身類別門檻的紀錄，或套用依情境而異的業務規則時非常有用。以下範例中，產品價格高於其類別平均值，意即低價配件可能被標記為昂貴，而較高價格的自行車則可能不會：

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS Category
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
)
ORDER BY pc.Name, p.ListPrice DESC;

你可以用同樣的模式來辨識行為與個人基線不同的顧客。

以下查詢可找出至少下過一筆超過其平均訂單價值的客戶，有助於辨識異常購買模式或高價值交易：

SELECT DISTINCT
    c.CustomerID,
    c.FirstName,
    c.LastName
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
WHERE soh.TotalDue > (
    SELECT AVG(soh2.TotalDue)
    FROM SalesLT.SalesOrderHeader AS soh2
    WHERE soh2.CustomerID = c.CustomerID
);

使用 `EXISTS` 搭配相關子查詢

運算子 EXISTS 結合相關子查詢，測試相關資料表中是否存在任何相符的列，回傳簡單的真或假結果。這種模式非常有效率，因為資料庫引擎可以在找到第一個匹配的列後立即停止搜尋。與回傳實際資料的子查詢不同，子 EXISTS 查詢只需確認有無。

當你需要回答像是「哪些顧客下過訂單？」或「哪些產品從未販售過？」這類問題時，請使用 EXISTS 。子查詢通常使用 SELECT 1 ，因為實際值並不重要：

-- Find customers who have placed at least one order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

-- Find customers who have never placed an order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

EXISTS 當你需要檢查結合多種條件的複雜條件時，價值就更高了。你可以在子查詢中加入任何篩選邏輯，而外部查詢只會包含至少有一列相符相關列的列。

以下範例展示了如何尋找訂單量多且類別多且每件產品都符合價格門檻的產品：

-- Find products that have been ordered in quantities greater than 10
SELECT p.ProductID, p.Name
FROM SalesLT.Product AS p
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderDetail AS sod
    WHERE sod.ProductID = p.ProductID
      AND sod.OrderQty > 10
);

-- Find categories where all products are priced above $100
SELECT pc.ProductCategoryID, pc.Name
FROM SalesLT.ProductCategory AS pc
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.Product AS p
    WHERE p.ProductCategoryID = pc.ProductCategoryID
      AND p.ListPrice <= 100
);

小提示

EXISTS 通常在子查詢時的表現優於 IN，尤其是在檢查大型資料表中存在性時。優化器可在找到第一個與的 EXISTS匹配後停止，但 IN 可能需要檢索所有匹配值。

在 `SELECT` 中使用相關子查詢計算值

在 SELECT 子句中的相關子查詢會為結果集的每一行計算一個單獨的值。此模式允許您將從相關資料表中彙整或衍生的值與主列細節一併包含，且不會將結果壓縮成群組。

這種方式在需要顯示情境資訊時特別有用，例如將每個產品與其類別的平均價格，或每位員工並列其部門總人數。子查詢每列執行一次，利用該列的值來篩選計算：

-- Show each product with its category's average price
SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS CategoryAvgPrice,
    p.ListPrice - (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS DifferenceFromAvg
FROM SalesLT.Product AS p;

你也可以使用此模式來計數相關紀錄或從相關資料表中擷取特定值。以下查詢會建立一個客戶摘要，包含每位客戶的訂單數量及最近一次訂單日期，這些日期分別針對每個客戶列計算：

-- Show each customer with their order count
SELECT 
    c.CustomerID,
    c.FirstName,
    c.LastName,
    (
        SELECT COUNT(*)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS OrderCount,
    (
        SELECT MAX(soh.OrderDate)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS LastOrderDate
FROM SalesLT.Customer AS c;

備註

子句中 SELECT 相關的子查詢必須恰好回傳一個值。若子查詢能回傳多列，則以彙總函數包裹，如 MAX()、 MIN()或 SUM()。

使用相關子查詢找出每組的前 N 項

相關子查詢最實用的應用之一是找出每組中排名前 N 個項目。這個模式回答了像是「每個類別中最貴的三個產品是什麼？」或「每個地區前五名的銷售人員是誰？」這樣的問題。

相關子查詢會檢視每一列，並透過檢查同一群組中排名較高的列數，判斷該列是否屬於該群組的前 N 個。這種方法在沒有視窗函式或需要複雜排序邏輯（視窗函數無法表達）時效果很好。

以下查詢是透過選擇 ID 出現在該類別前三名的產品，找出每個類別最昂貴的前三名產品：

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID IN (
    SELECT TOP 3 p2.ProductID
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
    ORDER BY p2.ListPrice DESC
)
ORDER BY pc.Name, p.ListPrice DESC;

另一種方法是計算有多少項目排名高於當前列。如果少於 N 個項目具有較高的值，則目前資料列屬於前 N 名。此技術會以不同方式處理同分情況；當您需要包含所有並列第 N 名的項目時特別有用：

-- Find products that are in the top 3 by price within their category
SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE (
    SELECT COUNT(*)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
      AND p2.ListPrice > p.ListPrice
) < 3
ORDER BY pc.Name, p.ListPrice DESC;

比較連續的列

相關子查詢可根據排序標準存取前一列或後續列的數值，實現跨期比較與趨勢分析。此模式對於計算連續記錄間的變化非常有用，例如將每個順序與前一順序比較，或追蹤數值隨時間演變。

子查詢透過篩選邏輯序列中出現在當前列之前（或之後）的列來尋找相關列，然後排序結果以獲得緊鄰列：

-- Show each order with the previous order's total
SELECT 
    soh.SalesOrderID,
    soh.OrderDate,
    soh.TotalDue,
    (
        SELECT TOP 1 soh2.TotalDue
        FROM SalesLT.SalesOrderHeader AS soh2
        WHERE soh2.CustomerID = soh.CustomerID
          AND soh2.OrderDate < soh.OrderDate
        ORDER BY soh2.OrderDate DESC
    ) AS PreviousOrderTotal
FROM SalesLT.SalesOrderHeader AS soh
ORDER BY soh.CustomerID, soh.OrderDate;

小提示

對於連續的列比較，像 LAG() 和 LEAD() 這樣的視窗函式通常比相關子查詢更有效率且易於閱讀。當你需要比視窗函數支援更複雜的條件時，使用相關的子查詢。

在相關子查詢與替代方案之間選擇

相關子查詢不一定是最佳方法。以下表格幫助你選擇合適的技巧：

請採用此方法	當你需要的時候......
相互關聯的子查詢	將每一列與根據該列屬性動態計算的值比較，或用 `EXISTS`/`NOT EXISTS`測試存在性，或用複雜選擇邏輯每列擷取一個相關值。
加入	從多個資料表擷取欄位，或當關係簡單且無需逐列計算時。
視窗函式	計算累計總數、排名，或存取上一列/下一列。`LAG()`/`LEAD()` 在這些模式下，比相關子查詢更有效率。
職業訓練（CTE）	重複參考相同的計算結果，或將複雜邏輯拆解成命名且可讀的步驟。

效能考量

相關子查詢若未正確優化，可能會影響效能。由於子查詢在外層查詢的每一列中執行一次，若相關查詢設計不佳，可能導致在大型資料表上執行成千上萬甚至數百萬次的子查詢。

請遵循以下指引以優化相關子查詢的效能：

建立關聯欄位索引：確保子查詢子句 WHERE 中連結回外部查詢的欄位皆有索引。例如，如果你的子查詢在上 ProductCategoryID有篩選，該欄位的索引可以讓資料庫快速找到匹配的列，而不必掃描整個表中每個外層列。
在索引中加入額外欄位：如果你的子查詢也會過濾或彙總其他欄位，可以考慮使用複合索引。在 (ProductCategoryID, ListPrice) 上建立索引，可在單一索引搜尋中，同時支援相關查詢，以及以價格為基礎的篩選或彙總。
評估替代方法：許多相關子查詢可以以更好的效能重寫為連接函數或視窗函式。如果你在尋找每組的最大值，一個視窗 ROW_NUMBER() 函式通常會比一個為每列選擇 MAX() 的相關子查詢表現更好。
檢視執行計畫：使用 SET STATISTICS IO ON 並檢視實際執行計畫，了解優化器如何處理你相關的子查詢。最佳化工具可能會在內部將其轉換為聯結；或依照撰寫方式，逐列執行。
使用實際資料量進行測試：在小型測試資料集上表現良好的相關子查詢，於正式環境規模的資料表上可能會變慢。部署到生產環境前，務必用具代表性的基準數據來做基準測試。

這很重要

處理大型資料表相關子查詢時，務必檢視執行計畫。優化器可以有效轉換它們，但複雜的相關性可能會從查詢重寫中受益。

欲了解更多子查詢資訊，請參閱子查詢（Transact-SQL）及EXISTS（Transact-SQL）。

意見反應

此頁面對您有幫助嗎？