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

Here is an APPLY example that cannot be rewritten as a JOIN:

SELECT ...
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

You can think at APPLY as a JOIN for correlated relations. JOIN does not allow for correlation, the two joined relations must be independent. For example, the following is incorrect:

select * 
from sys.objects o1
join (
    select * 
    from sys.objects o2
    where o1.object_id = o2.object_id) as o3
on 1=1;

Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "o1.object_id" could not be bound.

However, the same can be expressed as an APPLY:

select * 
from sys.objects o1
cross apply (
    select * 
    from sys.objects o2
    where o1.object_id = o2.object_id) as o3;

o1.object_id is available in the subquery, because APPLY allows for correlation. The most important use case is table valued functions, as in my original example, because APPLY allows for columns of the 'main' table to be passed in as parameters to the function.

The difference between CROSS APPLY and OUTER APPLY is exactly as with JOINs, the OUTER case allows for rows that do not match (for which the APPLY returns empty result set) to be returned, with NULLs in the corresponding inner table columns.

Answer 2

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.