相関サブクエリを使用して行を比較する
ヒント
詳細については、「 テキストと画像 」タブを参照してください。
相関クエリは、外部クエリの列を参照するサブクエリであり、外部クエリによって処理された行ごとにサブクエリが 1 回実行される依存関係が作成されます。 これは非効率的に聞こえるかもしれませんが、相関クエリは、行ごとの比較や、それ以外の表現が困難または不可能な計算に役立ちます。
相関サブクエリの実行について
相関サブクエリは、外部クエリから 1 つ以上の列を参照し、2 つの間に論理依存関係を作成します。 1 回実行して固定結果を返す通常のサブクエリとは異なり、相関サブクエリは外側のクエリが処理する行ごとに 1 回繰り返し実行されます。
これは入れ子になったループと考えてください。外側のクエリの各行に対して、データベースはその行の値を使用してサブクエリを評価します。 この動作により、行ごとの強力な比較が可能になりますが、効率的なクエリを記述するには実行モデルを理解する必要もあります。
次の 2 つのクエリの違いを考えてみましょう。
-- 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
);
非相関の例では、サブクエリはすべての製品の 1 つの平均価格を計算します。 この値は 1 回計算され、各製品の価格がその固定数と比較されます。
相関付けられた例では、サブクエリは外部クエリから p1.ProductCategoryID 参照します。 これにより、依存関係が作成されます。製品行ごとに、サブクエリはその特定のカテゴリの平均価格を計算します。 "Bikes" カテゴリの製品は自転車の平均と比較され、"アクセサリ" の製品はアクセサリの平均と比較されます。
注
クエリ オプティマイザーは、多くの場合、相関サブクエリを内部的に同等の結合に変換します。 ただし、論理相関動作を理解することは、物理的な実行が異なる場合でも、正しいクエリを記述するのに役立ちます。
関連付けられたサブクエリを使用したフィルター処理
WHERE句の相関サブクエリを使用すると、静的比較では不可能な行固有のフィルター条件が有効になります。 1 つの固定値と比較する代わりに、各行は、その行の属性に基づいて動的に計算された値に対して評価されます。
このパターンは、グループ内の外れ値を特定したり、独自のカテゴリのしきい値を超えるレコードを見つけたり、コンテキストによって異なるビジネス ルールを適用したりする必要がある場合に便利です。 次の例では、カテゴリ平均を上回る価格の製品が見つかります。つまり、低価格のアクセサリには高価なフラグが設定されている一方で、価格の高い自転車では価格が高くない可能性があります。
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;
同じパターンを適用して、個人のベースラインと動作が異なる顧客を識別できます。
次のクエリでは、独自の平均注文値を超える注文を少なくとも 1 つ行った顧客を検索します。これは、異常な購入パターンや価値の高いトランザクションを特定するのに役立ちます。
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 演算子は、一致する行が関連するテーブルに存在するかどうかをテストし、単純な true または false の結果を返します。 このパターンは、データベース エンジンが最初に一致する行を見つけたらすぐに検索を停止できるため、非常に効率的です。 実際のデータを返すサブクエリとは異なり、 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 は、複数の条件を組み合わせた複雑な条件を確認する必要がある場合に、さらに価値が高くなります。 サブクエリ内に任意のフィルター処理ロジックを追加できます。外側のクエリには、少なくとも 1 つの一致する関連行が存在する行のみが含まれます。
次の例は、すべての製品が価格しきい値を満たす高数量の注文とカテゴリを持つ製品を見つける方法を示しています。
-- 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句の相関サブクエリは、結果セット内の行ごとに個別の値を計算します。 このパターンでは、結果をグループに折りたたまずに、関連テーブルの集計値または派生値をメイン行の詳細と共に含めることができます。
このアプローチは、各製品をカテゴリの平均価格と共に表示したり、各従業員を部門の総人員と共に表示したりするなど、コンテキスト情報を表示する必要がある場合に便利です。 サブクエリは行ごとに 1 回実行され、その行の値を使用して計算をフィルター処理します。
-- 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句の相関サブクエリは、正確に 1 つの値を返す必要があります。 サブクエリが複数の行を返す可能性がある場合は、 MAX()、 MIN()、 SUM()などの集計関数でラップします。
相関サブクエリを持つグループごとの上位 N を検索する
相関サブクエリの最も実用的なアプリケーションの 1 つは、各グループ内の上位 N 個の項目を見つけることです。 このパターンでは、"各カテゴリで最も高価な 3 つの製品は何ですか?" や "各リージョンの上位 5 人の営業担当者は誰ですか?
相関サブクエリは、各行を調べ、同じグループ内の他の行のランクが高い行の数を調べることで、そのグループの上位 N に属しているかどうかを判断します。 この方法は、ウィンドウ関数が使用できない場合や、ウィンドウ関数で表現できない複雑なランク付けロジックが必要な場合に適しています。
次のクエリでは、ID がカテゴリの上位 3 に表示される製品を選択して、カテゴリごとに最もコストの高い上位 3 つの製品を検索します。
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で存在するかどうかをテストするか、複雑な選択ロジックを使用して行ごとに 1 つの関連値を取得します。 |
| 結合 | 複数のテーブルから列を取得するか、行単位の計算なしでリレーションシップが単純な場合に取得します。 |
| ウィンドウ関数 |
LAG()
/
LEAD()を使用して、実行中の合計、ランク付け、または前/次の行へのアクセスを計算します。 これらのパターンの相関サブクエリよりも効率的です。 |
| CTE | 同じ計算結果を複数回参照するか、複雑なロジックを名前付きの読み取り可能な手順に分割します。 |
パフォーマンスに関する考慮事項
相関サブクエリは、正しく最適化されていない場合にパフォーマンスに影響を与える可能性があります。 サブクエリは外側のクエリの行ごとに 1 回実行されるため、不適切に設計された相関クエリでは、大きなテーブルに対して数千または数百万のサブクエリが実行される可能性があります。
相関サブクエリのパフォーマンスを最適化するには、次のガイドラインに従います。
関連付け列にインデックスを作成する: サブクエリの
WHERE句で参照されている列が、外部クエリにリンクするインデックスが作成されていることを確認します。 たとえば、サブクエリがProductCategoryIDでフィルター処理する場合、その列のインデックスを使用すると、外部行ごとにテーブル全体をスキャンするのではなく、一致する行をすばやく検索できます。インデックスに追加の列を含める: サブクエリが他の列に対してもフィルター処理または集計を行う場合は、複合インデックスを検討してください。
(ProductCategoryID, ListPrice)のインデックスは、1 つのインデックス シークでの相関検索と価格ベースのフィルター処理または集計の両方をサポートします。代替アプローチを評価する: 多くの相関サブクエリは、より優れたパフォーマンスで結合またはウィンドウ関数として書き換えることができます。 グループあたりの最大値を見つける場合、多くの場合、
ROW_NUMBER()を持つウィンドウ関数は、各行のMAX()を選択する相関サブクエリよりも優れている場合があります。実行プランを確認する:
SET STATISTICS IO ONを使用し、実際の実行プランを調べて、オプティマイザーが相関サブクエリをどのように処理するかを理解します。 オプティマイザーは、内部的に結合に変換したり、書き込まれたように行ごとに実行したりする場合があります。現実的なデータ ボリュームを使用したテスト: 小規模なテスト データセットで適切に実行される相関サブクエリは、実稼働サイズのテーブルでは遅くなる可能性があります。 運用環境にデプロイする前に、必ず代表的なデータを使用してベンチマークを実行してください。
Important
大きなテーブルで相関サブクエリを使用する場合は、常に実行プランを確認してください。 オプティマイザーはそれらを効率的に変換できますが、複雑な相関関係はクエリの書き換えの恩恵を受ける可能性があります。
サブクエリの詳細については、「 サブクエリ (Transact-SQL) と EXISTS (Transact-SQL)」を参照してください。