SQL Server Guide APPLY AND JOIN Keyword

Question

I read an article about using keywords apply & join. See Some SQL, where one example uses an internal join and the other uses a keyword.

Here is the pic table

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 queries return the same output and the same execution plan. Here is a photo

Use the external application again and the left external connection

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 requests produce the same output and the same execution plan. Therefore, I just do not understand in what situation should I use OUTER APPLYor CROSS APPLYinstead of an internal connection or an external external connection?

therefore, if possible, the same scenario will come where you should use OUTER APPLY or CROSS APPLYthanks

Answer 1

, , - . , split() ( ). - , :

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

-, SQL.

- , :

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

, , . :

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

, , ( ). table2 , , .

, join, . , 10 "", , from. - SQL .

Answer 2

APPLY, JOIN:

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

, APPLY . JOIN , . , :

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.

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 , APPLY . , , , APPLY "" .

CROSS APPLY OUTER APPLY JOINs, OUTER , ( APPLY ), NULL .