SQL Server: When We Use Cross or Outer Apply Instead of Simple Joins

APPLY statements are introduced in SQL SERVER 2005. 

I think most of us are use to with common JOIN statements. APPLY statements are just like these JOIN statements. 

CROSS APPLY is just like INNER JOIN and OUTER APPLY is just like LEFT OUTER JOIN.

Difference is very simple. 

When both joining members are simple tables, we use JOIN, but when one of joining member is parametrized table valued function, and there is no possibility to use ON clause, we use APPLY statements for joining.

Arshad Ali has explained it nicely with examples.