[sql server] cross apply

inner join, left join, cross apply 의 결과 차이를 확인

-----------------------------------------------------------------------------------------

if object_id('tempdb..#test') is not null

drop table #test

if object_id('tempdb..#testOne') is not null

drop table #testOne

create table #test (id int, ename nvarchar(20))

create table #testOne (id int, test_id int, pname nvarchar(20))

insert into #test

values (1, 'Test'), (2, 'Test1'), (3, 'Test2')

insert into #testOne

values (1, 1, 'Test3'), (2, 1, 'Test4'), (3, 2, 'Test5')

--inner join

select a.*, b.*

from #test a

inner join #testOne b on a.id = b.test_id

--left outer join    

select a.*, b.*

from #test a

left join #testOne b on a.id = b.test_id

--cross apply

select t.id, t.ename, p.pname

from #test t

cross apply (select pname from #testOne) p

select t.id, t.ename, p.pname

from #test t

cross apply (select pname from #testOne p where p.test_id = t.id) p

select t.id, t.ename, p.pname

from #test t

cross apply (select pname from #testOne p inner join #test tt on tt.id = p.test_id where tt.id = 2) p

select t.id, t.ename, p.pname

from #test t

cross apply (select pname from #testOne) p

where t.id = 2

출처 : https://stackoverrun.com/ko/q/11330120