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