自己完結型または相関のサブクエリを使用する

3 分

これまで確認したサブクエリは自己完結型です。内側のクエリが外側のクエリから独立しており、実行は 1 回で、その結果が外側のクエリに返されるものです。 T-SQL では、"相関" サブクエリもサポートされています。内側のクエリが外側のクエリの列を参照し、概念的には行ごとに 1 回実行されるものです。

相関サブクエリを使用する

自己完結型サブクエリと同様に、相関サブクエリは、外側のクエリ内で入れ子になっている SELECT ステートメントです。また、相関サブクエリは、スカラーサブクエリにすることも複数値サブクエリにすることもできます。通常、内側のクエリが外側のクエリの値を参照する必要がある場合に使用されます。

ただし、自己完結型サブクエリとは異なり、相関サブクエリを使用する場合は特別な考慮事項がいくつかあります。

相関サブクエリは、外側のクエリと切り離して実行することはできません。この制限により、テストとデバッグが複雑になります。
1 回だけ処理される自己完結型サブクエリとは異なり、相関サブクエリは複数回実行されます。論理的には、外側のクエリが最初に実行され、返された行ごとに、内側のクエリが処理されます。

次の例では、相関サブクエリを使用して、各顧客の最新の注文を返しています。このサブクエリは、外側のクエリを参照し、その WHERE 句で CustomerID 値を参照します。外側のクエリの行ごとに、サブクエリが、その行で参照されている顧客の最大の注文 ID を見つけ、外側のサブクエリが、今確認している行がその注文 ID を持つ行であるかどうかをチェックします。

SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader AS o2
     WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;

相関サブクエリを記述する

相関サブクエリを記述するには、次のガイドラインを考慮してください。

外側のクエリを、内側のクエリの適切な戻り結果を受け入れるように記述します。内側のクエリがスカラーの場合は、WHERE 句で =、<、>、<> などの等値演算子と比較演算子を使用できます。内側のクエリが複数の値を返す可能性がある場合は、IN 述語を使用します。 NULL の結果を処理するように計画します。
外側のクエリから、相関サブクエリによって参照される列を特定します。外側のクエリ内の列のソースであるテーブルのエイリアスを宣言します。
内側のテーブルから、外側のテーブルの列と比較される列を特定します。外側のクエリの場合と同様に、ソーステーブルのエイリアスを作成します。
内側のクエリを、外側のクエリからの入力値に基づいて、そのソースから値を取得するように記述します。たとえば、内側のクエリの WHERE 句で外側の列を使用します。

内側のクエリと外側のクエリの間の相関関係は、外側の値が比較のために内側のクエリによって参照されるときに発生します。このサブクエリの名前は、この相関関係から来ています。

EXISTS を使用する

T-SQL では、サブクエリから値を取得する以外に、クエリから結果が返されるかどうかを確認するためのメカニズムが用意されています。 EXISTS 述語を使用して、指定した条件を満たしている行が存在するかどうかを判別しますが、これは行ではなく、TRUE または FALSE を返します。この手法は、結果の取得と処理のオーバーヘッドを発生させることなく、データを検証するのに役立ちます。

サブクエリが EXISTS 述語を使用して外側のクエリに関連付けられている場合、そのサブクエリの結果は、SQL Server によって特別な方法で処理されます。サブクエリからスカラー値または複数値リストを取得するのではなく、EXISTS を使用して、単に結果に行が存在するかどうかをチェックします。

概念的には、EXISTS 述語は、結果を取得し、返された行をカウントし、その数と 0 を比較するのと同じです。次のクエリを比較してください。どちらも、注文を行った顧客に関する詳細を返します。

最初のクエリ例では、サブクエリ内で COUNT を使用しています。

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE
(SELECT COUNT(*) 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID) > 0;

2 番目のクエリは、同じ結果を返しますが、EXISTS を使用しています。

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE EXISTS
(SELECT * 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID);

最初の例のサブクエリは、その顧客が注文を行ったことがあるのを示すためだけに、Sales.SalesOrderHeader テーブル内で見つかった custid ごとにすべての出現回数をカウントし、カウント結果と 0 を比較する必要があります。

2 番目のクエリでは、関連する注文が Sales.SalesOrderHeader テーブル内で見つかった時点で、EXISTS によって、custid に TRUE が返されます。出現ごとの完全なカウントは不要です。また、EXISTS 形式では、サブクエリが単一の列を返すことに制限されていないことにも注意してください。ここでは、SELECT * を使用しています。返された列は関係ありません。ここでチェックするのは行が返されたかどうかだけで、その行にどんな値があるかはチェックしないからです。

論理的な処理の観点から見ると、この 2 つのクエリ形式は同じです。パフォーマンスの観点からは、クエリは、実行のためにデータベースエンジンによって最適化されるため、異なる方法で処理される可能性があります。ご自身の用途に合わせてそれぞれテストすることを検討してください。

注意

COUNT(*) を使うサブクエリを EXISTS を使うものに変換する場合は、サブクエリが、SELECT COUNT(*) ではなく、SELECT * を使っていることを確認してください。 SELECT COUNT(*) は常に行を返し、EXISTS は常に TRUE を返します。

EXISTS のもう 1 つの便利な使い方は、次の例に示すように、NOT を使用してサブクエリを否定することです。こうすると、注文を行ったことがない顧客が返されます。

SELECT CustomerID, CompanyName, EmailAddress 
FROM SalesLT.Customer AS c 
WHERE NOT EXISTS
  (SELECT * 
   FROM SalesLT.SalesOrderHeader AS o
   WHERE o.CustomerID = c.CustomerID);

SQL Server から、注文を行った顧客の関連注文に関するデータが返される必要がなくなります。 custid が Sales.SalesOrderHeader テーブル内で見つかると、NOT EXISTS は FALSE と評価され、評価が短時間で完了します。

サブクエリで EXISTS を使用するクエリを記述するには、次のガイドラインを考慮してください。

キーワード EXISTS は、WHERE の直後に記述します。 NOT も使用する場合を除き、その前に列名 (または他の式) は記述しません。
サブクエリ内で、SELECT * を使用します。このサブクエリから行は返されないため、列を指定する必要がありません。

続行

相関サブクエリを使用する

相関サブクエリを記述する

EXISTS を使用する

フィードバック