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
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
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.