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

As me and my fellow DBAs are regular visitors of SQL Authority. In his last post, Pinal Dave introduced a query regarding wait stats and used CROSS APPLY in his query. A friend of mine asked, “What is The Difference Between APPLY Statements and Common Join Statements”.

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.