Correlated subqueries are subqueries that refer to attributes from the table that appears in the outer query. This means that the subquery is dependent on the outer query, and cannot be invoked independently. Logically, it’s as if the subquery is evaluated separately for each outer row. For example, the query in Example 4-1 returns orders with the maximum order ID for each customer:
Example 4-1. Correlated Subquery
USE TSQLFundamentals2008; SELECT custid, orderid, orderdate, empid FROM Sales.Orders AS O1 WHERE orderid = (SELECT MAX(O2.orderid) FROM Sales.Orders AS O2 WHERE O2.custid = O1.custid);
The outer query is against an instance of the Orders table called O1; it filters orders where the order ID is equal to the value returned ...