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.