How to use join smartly

Just look at the below queries

A :

declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
insert into @t(id, data1, data2)
values(1, 200, 250)
insert into @t(id, data1, data2)
values(1, 300, 350)

insert into @b(id, value1, value2)
values(1, 1000, 1050)
 insert into @b(id, value1, value2)
values(1, 2000, 2050)
 insert into @b(id, value1, value2)
values(1, 3000, 3050)


select x. id, x.data1, x.data2, y.value1, y.value2 from
(
select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
full outer join
(
select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr

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

B :

declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)


insert into @b(id, value1, value2)
values(1, 1000, 1050)
 insert into @b(id, value1, value2)
values(1, 2000, 2050)
 insert into @b(id, value1, value2)
values(1, 3000, 3050)


select x. id, x.data1, x.data2, y.value1, y.value2 from
(
select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
full outer join
(
select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr

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

C :

declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
insert into @t(id, data1, data2)
values(1, 200, 250)
insert into @t(id, data1, data2)
values(1, 300, 350)




select x. id, x.data1, x.data2, y.value1, y.value2 from
(
select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
full outer join
(
select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr

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


D :

declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
--insert into @t(id, data1, data2)
--values(1, 200, 250)
--insert into @t(id, data1, data2)
--values(1, 300, 350)

insert into @b(id, value1, value2)
values(1, 1000, 1050)
 insert into @b(id, value1, value2)
values(1, 2000, 2050)
 insert into @b(id, value1, value2)
values(1, 3000, 3050)


select x. id, x.data1, x.data2, y.value1, y.value2 from
(
select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
full outer join
(
select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr

----------------------------------------<<<CTE >>--------------------------------------------------------------------------

E :

The Same thing works with CTE(i have included the table2 id too)




declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
--insert into @t(id, data1, data2)
--values(1, 200, 250)
--insert into @t(id, data1, data2)
--values(1, 300, 350)

insert into @b(id, value1, value2)
values(1, 1000, 1050)
 insert into @b(id, value1, value2)
values(1, 2000, 2050)
 insert into @b(id, value1, value2)
values(1, 3000, 3050)


; with c as 
(
select ROW_NUMBER() over(partition by t.id order by t.id) r, t.id, t.data1, t.data2 from @t t
),e as 
(
select ROW_NUMBER() over(partition by b.id order by b.id) r, b.id, b.value1, b.value2 from @b b
)
select c.id,c.data1,c.data2,e.id,e.value1,e.value2
from c 
full outer join e on c.r = e.r
order by c.id desc

Good Luck.. 

Kindly give the feedback if it helped you :)