相関サブクエリ

多くのクエリは、サブクエリを 1 回実行し、その結果である 1 つまたは複数の値を外側のクエリの WHERE 句に代入することにより評価されます。相関サブクエリ (繰り返しサブクエリとも呼びます) を含むクエリでは、サブクエリの値は外側のクエリによって決まります。つまり、外側のクエリで選択される各行に対して 1 回ずつ、サブクエリが繰り返し実行されることになります。

次のクエリでは、各従業員の姓と名を検索し、その中から SalesPerson テーブルで示される特別手当の値が 5,000 で、従業員 ID が Employee テーブルと SalesPerson テーブルで一致しているものを取得しています。

USE AdventureWorks2008R2;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID 
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID 
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

以下に結果セットを示します。

LastName FirstName BusinessEntityID

-------------------------- ---------- ------------

Ansman-Wolfe Pamela 280

Saraiva José 282

(2 行処理されました)

上記のステートメントのサブクエリは、外側のクエリから独立して評価できません。このサブクエリでは Employee.BusinessEntityID の値が必要ですが、その値は SQL Server により Employee テーブルの他の行が調べられると、それに応じて変化します。

このクエリでは、SQL Server により、Employee テーブルの各行の値が内側のクエリに代入されることによって、各行を結果に含めるかどうかが判断されます。たとえば、SQL Server により、最初に Syed Abbas の行が調べられると、SQL Server により内側のクエリに代入された値 285 が変数 Employee.BusinessEntityID で取得されます。

USE AdventureWorks2008R2;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;

結果は 0 なので (Syed Abbas は販売員ではないので特別手当が出ません)、外側のクエリは次のように評価されます。

USE AdventureWorks2008R2;
GO
SELECT LastName, FirstName
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID 
WHERE 5000 IN (0.00)

これは偽なので、Syed Abbas の行は結果に含められません。同じ手順を Pamela Ansman-Wolfe の行に対して実行します。この行が結果に含められることがわかります。

相関サブクエリでは、特定のテーブルに含まれている列をテーブル値関数の引数として外側のクエリで参照することにより、FROM 句にテーブル値関数を含めることもできます。この場合、外側のクエリの各行について、サブクエリに従ってテーブル値関数が評価されます。