What is Use self-contained or correlated subqueries

Previously, we looked at self-contained subqueries; in which the inner query is independent of the outer query, executes once, and returns its results to the outer query. T-SQL also supports correlated subqueries, in which the inner query references column in the outer query and conceptually executes once per row.

Working with correlated subqueries

Like self-contained subqueries, correlated subqueries are SELECT statements nested within an outer query. Correlated subqueries may also be either scalar or multi-valued subqueries. They’re typically used when the inner query needs to reference a value in the outer query.

However, unlike self-contained subqueries, there are some special considerations when using correlated subqueries:

• Correlated subqueries cannot be executed separately from the outer query. This restriction complicates testing and debugging.
• Unlike self-contained subqueries, which are processed once, correlated subqueries will run multiple times. Logically, the outer query runs first, and for each row returned, the inner query is processed.

The following example uses a correlated subquery to return the most recent order for each customer. The subquery refers to the outer query and references its CustomerID value in its WHERE clause. For each row in the outer query, the subquery finds the maximum order ID for the customer referenced in that row, and the outer query checks to see if the row it’s looking at is the row with that order ID.

SQLCopy

SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader AS o2
     WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;