相関サブクエリ
多くのクエリは、サブクエリを 1 回実行し、その結果である 1 つまたは複数の値を外側のクエリの WHERE 句に代入することにより評価されます。相関サブクエリ (繰り返しサブクエリとも呼びます) を含むクエリでは、サブクエリの値は外側のクエリによって決まります。つまり、外側のクエリで選択される各行に対して 1 回ずつ、サブクエリが繰り返し実行されることになります。
次のクエリでは、各従業員の姓と名を検索し、その中から SalesPerson テーブルで示される特別手当の値が 5,000 で、従業員 ID が Employee テーブルと SalesPerson テーブルで一致しているものを取得しています。
USE AdventureWorks;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.EmployeeID
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson sp
WHERE e.EmployeeID = sp.SalesPersonID) ;
GO
以下に結果セットを示します。
LastName FirstName EmployeeID
------------------------- ----------------- -----------
Ansman-Wolfe Pamela 280
Saraiva José 282
(2 row(s) affected)
上記のステートメントのサブクエリは、外側のクエリから独立して評価できません。このサブクエリでは Employee.EmployeeID の値が必要ですが、その値は SQL Server により Employee テーブルの他の行が調べられると、それに応じて変化します。
このクエリでは、SQL Server により、Employee テーブルの各行の値が内側のクエリに代入されることによって、各行を結果に含めるかどうかが判断されます。たとえば、SQL Server により、最初に Syed Abbas の行が調べられると、SQL Server により内側のクエリに代入された値 288 が変数 Employee.EmployeeID で取得されます。
USE AdventureWorks;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE SalesPersonID = 288;
結果は 0 なので (Syed Abbas は販売員ではないので特別手当が出ません)、外側のクエリは次のように評価されます。
USE AdventureWorks;
GO
SELECT LastName, FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE 5000 IN (0.00);
これは偽なので、Syed Abbas の行は結果に含められません。同じ手順を Pamela Ansman-Wolfe の行に対して実行します。この行が結果に含められることがわかります。
相関サブクエリでは、特定のテーブルに含まれている列をテーブル値関数の引数として外側のクエリで参照することにより、FROM 句にテーブル値関数を含めることもできます。この場合、外側のクエリの各行について、サブクエリに従ってテーブル値関数が評価されます。