MS SQL 2005 and up adds support for the APPLY clause, which, in turn, lets you join a table to any dynamic sets, such as table-valued-functions or even derived tables. While we can argue over the benefits and dangers of the latter (another set of article perhaps?), being able to do things more than one way is certainly always awesome.
The two new functions (CROSS APPLY and OUTER APPLY) are essentially INNER and OUTER JOINs for table-functions (you cannot directly join them like tables). Here is just a simple example of the usage (don’t hesitate to expand on it):
SELECT All.CustomerID FROM Customer as C CROSS APPLY fnSelectAllChildren(C.CustomerID) AS All WHERE C.Status = 1
This technique can also be used in more complex queries alongside derived tables, FOR XML, etc., covering the need for things such as inline multi-row CONCAT (multiple rows into a single column):
SELECT ito.ID, ( SELECT TOP 1 ISNULL(LEFT(o.list, LEN(o.list) - 1), 'Unknown') FROM Orders AS ito2 CROSS APPLY ( SELECT CONVERT(VARCHAR(12), ServiceType) + ',' AS FROM OrderService AS itos2 (NOLOCK) JOIN OrderServiceType AS itost (NOLOCK) ON itost.Id = itos2.OrderServiceType WHERE itos2.OrderId = ito.ID FOR XML PATH('') ) o (list) WHERE ito2.ID = ito.ID ) AS AllTypes FROM Orders (NOLOCK) ito WHERE ito.Status = 0