Correlated Subqueries

A frequent question posted on here is something like "How do I list all of my customers along with their most recent order". You can achieve this using a correlated subquery. A correlated subquery is a query within a query that references or 'correlates' with the outer query.

e.g.
SELECT CustomerID,
(SELECT TOP 1 OrderID FROM Orders WHERE CustomerID=C.CustomerID ORDER BY OrderDate DESC) AS LastOrderID
FROM Customers C

Note that we alias the outer query - in this case it is called 'C'. The subquery pulls the most recent order, matching the CustomerID from the outer query.

Another example with an INNER JOIN in the subquery:
SELECT CustomerID,
(SELECT TOP 1 Orders.OrderID FROM Orders INNER JOIN OrderItems ON Order.OrderID = OrderItems.OrderID WHERE Orders.CustomerID=C.CustomerID AND OrderItems.ItemID=6 ORDER BY Orders.OrderDate DESC) AS LastOrderID
FROM Customers C

This will produce a list of all customers along with the most recent order they placed for Item number 6.