Correlated Subqueries

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

Get Microsoft® SQL Server® 2008 T-SQL Fundamentals now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.