Guideline for SQL Server APPLY AND JOIN Keyword

Question

I was reading an article on using apply & join keywords. See some SQL where one example uses inner join & other use apply keyword.

Here is table pic

SELECT E.EMPID, E.NAME, E.DEPTID, D.NAME 
FROM EMPLOYEE E 
INNER JOIN  DEPARTMENT D ON E.DEPTID = D.DEPTID

SELECT E.EMPID, E.NAME, E.DEPTID, CA.NAME 
FROM EMPLOYEE E
CROSS APPLY
    (SELECT * FROM DEPARTMENT D WHERE D.DEPTID = E.DEPTID) CA  

Both the queries return the same output and same execution plan. Here is the pic

Again use outer apply and left outer join

SELECT E.EMPID, E.NAME, E.DEPTID, D.NAME 
FROM EMPLOYEE E 
LEFT JOIN  DEPARTMENT D ON E.DEPTID = D.DEPTID

SELECT E.EMPID, E.NAME, E.DEPTID, OA.NAME 
FROM EMPLOYEE E
OUTER APPLY
    (SELECT * FROM DEPARTMENT D WHERE D.DEPTID = E.DEPTID) OA

Now again both queries produce same output and same execution plan. So I just do not understand in what kind of situation one should use OUTER APPLY or CROSS APPLY instead of inner join or left outer join?

so if possible come with same scenario where one should use OUTER APPLY or CROSS APPLY thanks

Answer 1

A relatively common thing that you want to do is to split a column into multiple values based on a splitter. So, there are various split() functions on the web (lots and lots, actually). Here is a small example derived from this random answer to such a question:

  SELECT d.RawKey, d.delimitedstring, d.delimitedvalues, 
         c.items SplitString, 
         c.rn
  FROM dbo.tblRawData d CROSS APPLY
       dbo.Split(d.DelimitedString, ',') c

This would be the killer app, because there is no other way to do this in a SQL statement.

Another case would be to return multiple values where you might have a correlated subquery:

select t1.*,
       (select t2.col from table2 t2 where t2.col1 = t1.col2) as newcol
from table1 t1;

Easy enough, but if you want to return multiple columns, you would need multiple subqueries. Instead:

select t1.*, t2.*
from table1 t1 cross apply
     (select t2.col1, t2.col3, t2.col4
      from table2 t2
      where t2.col1 = t1.col2
     ) t2;

This could probably be written using other operators (especially in this simple case). But if table2 is really multiple tables join'ed together with other logic, it can be quite complicated to rewrite this.

In general, though, you should stick with join where appropriate. Maybe in 10 years, that will seem "old fashioned" like the , in a from clause. But for now, joins are a key idea in SQL and the processing of data.