The real magic happens when you use SQL Server 2005′s new APPLY clause. The APPLY clause let’s you join a table to a table-valued-function.
I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table.
CROSS APPLY, despite looking like a JOIN, actually is applying a correlated sub-query. This imposes both the advantages of a correlated sub-query but also the performance implications.