Tuesday, June 25, 2013

T-SQL joins

What's the difference between an inner and full join in T-SQL? Or a right versus left join? I never have this at the top of my head when I need it, so for future references I've assembled a little example that shows the resulting difference between them.
Given the following t-sql:
declare @t1 table (id int)
declare @t2 table (id int)
 
insert into @t1 values(1),(2),(3)
insert into @t2 values(3),(4)
 
select 't1' as 'Table name', * from @t1
select 't2' as 'Table name', * from @t2
 
select 'inner join' as 'Join', t1.id as 'Left', t2.id as 'Right'
from @t1 as t1 inner join @t2 as t2 on t1.id = t2.id
 
select 'left join' as 'Join', t1.id as 'Left', t2.id as 'Right'
from @t1 as t1 left join @t2 as t2 on t1.id = t2.id
 
select 'right join' as 'Join', t1.id as 'Left', t2.id as 'Right'
from @t1 as t1 right join @t2 as t2 on t1.id = t2.id
 
select 'full join' as 'Join', t1.id as 'Left', t2.id as 'Right'
from @t1 as t1 full join @t2 as t2 on t1.id = t2.id

This is the result from the joins: