Correlated Subqueries

Any 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, 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

And the result is,

LastName FirstName EmployeeID

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

Ansman-Wolfe Pamela 280

Saraiva José 282

(2 row(s) affected)

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.