none
what different between inner join and cross apply ? RRS feed

  • Question

  • I work on sql server 2012 

    I see more queries it used cross apply 

    what mean cross apply 

    and what different between both

    I need answer i search more but not different 

    are this correct ?

    can you please show me different 

    Monday, January 20, 2020 7:27 PM

All replies

  • Hope this article "INNER JOIN vs. CROSS APPLY" could help. 

    A Fan of SSIS, SSRS and SSAS

    Monday, January 20, 2020 7:42 PM
  • When you say
      A INNER JOIN B,
    B cannot refer to A or another table source previously mentioned in the query. On the other hand, when you say:

      A CROSS APPLY B

    B can refer to A and other table sources earlier in the query

    With JOIN you must have an ON clause that specifies how A and B are related. With CROSS APPLY B is in most cases a correlated subquery. It can also be a function table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, January 20, 2020 10:32 PM
  • Hi engahmedbarbary,

    I've given you an example that shows you the same and the different in a very simple way. Also, it will have some differences in performance , it depends your actual script.  I hope it helps.

    create table #T(name varchar(10))
    insert into #T values('Sam')
    insert into #T values('Lily')
    insert into #T values(NULL )
    
    create table #T2(name varchar(10) , subject varchar(10) , score int)
    insert into #T2 values('Sam' , 'language' , 74)
    insert into #T2 values('Sam' , 'math' , 83)
    insert into #T2 values('Sam' , 'english' , 93)
    insert into #T2 values(NULL , 'math' , 50)
    
    --------the same part
    select * from #T a
    cross apply (select subject,score from #t2 where name=a.name) b
    
    select a.*,b.subject,b.score from #T a
    inner join #t2 b on a.name=b.name
    /*
    name       subject    score
    ---------- ---------- -----------
    Sam        language   74
    Sam        math       83
    Sam        english    93
    */
    
    ----the different part : inner join + table_name , cross apply function_name 
    go
    create table test 
    ([Part type] varchar(50),
    MachineName varchar(50))
    insert into test values 
    ('0,4,0,10,0','922857385'),
    ('0,4,0','930527914')
    
    SELECT t.MachineName,
    row_number()over (partition by MachineName order by (select 1 )) [index],
    rtrim(ltrim(v.val)) as SQLColumns
    FROM test t
    CROSS APPLY ParseValues(t.[Part type], ',')v
    /*
    MachineName                                        index                SQLColumns
    -------------------------------------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    922857385                                          1                    0
    922857385                                          2                    4
    922857385                                          3                    0
    922857385                                          4                    10
    922857385                                          5                    0
    930527914                                          1                    0
    930527914                                          2                    4
    930527914                                          3                    0
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 21, 2020 3:03 AM