Correlated Subqueries
Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.
This query retrieves one instance of each employee's first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables.
USE AdventureWorks;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson sp
WHERE e.EmployeeID = sp.SalesPersonID) ;
GO
Here is the result set.
LastName FirstName
Ansman-Wolfe Pamela
Saraiva Jos
(2 row(s) affected)
The previous subquery in this statement cannot be evaluated independently of the outer query. It needs a value for Employee.EmployeeID, but this value changes as Microsoft SQL Server 2005 examines different rows in Employee.
That is exactly how this query is evaluated: SQL Server considers each row of the Employee table for inclusion in the results by substituting the value in each row into the inner query. For example, if SQL Server first examines the row for Syed Abbas, the variable Employee.EmployeeID takes the value 288, which SQL Server substitutes into the inner query.
USE AdventureWorks;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE SalesPersonID = 288
The result is 0 (Syed Abbas did not receive a bonus because he is not a sales person), so the outer query evaluates to:
USE AdventureWorks
SELECT LastName, FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE 5000 IN (5000)
Because this is false, the row for Syed Abbas is not included in the results. Go through the same procedure with the row for Pamela Ansman-Wolfe. You will see that this row is included in the results.
Correlated subqueries can also include table-valued functions in the FROM clause by referencing columns from a table in the outer query as an argument of the table-valued function. In this case, for each row of the outer query, the table-valued function is evaluated according to the subquery.