[Solved] How do I join on multiple columns in SQL Server and include columns in one table that aren’t present in other tables?

by 


declare @t1 table (
col_a varchar(5) null
,col_b varchar(5) null
,col_c varchar(5) null
,col_d varchar(5) null
)

declare @t2 table (
col_a varchar(5) null
,col_b varchar(5) null
,col_e varchar(5) null
)


insert into @t1 values 
('Cat 1','Bla a','C-1','D-1')
,('Cat 1','Bla a','C-2','D-2')
,('Cat 1','Bla a','C-3','D-3')
,('Cat 2','Bla b','C-4','D-4')
,('Cat 2','Bla b','C-5','D-5')

insert into @t2 values 
('Cat 1'   , 'Bla a' , 'E-1'   )
,('Cat 2'   , 'Bla b' , 'E-2  ' )
,('Cat 2'   , 'Bla b' , 'E-3'   )
,('Cat 2'   , 'Bla b' , 'E-4')



select isnull(a.col_a,b.col_a) col_a, isnull(a.col_b,b.col_b) col_b, a.col_c,a.col_d,b.col_e
from (
    select *,row_number() over (partition by col_a order by col_c) rown
    from @t1
) a
full outer join (
    select *,row_number() over (partition by col_a order by col_e) rown
    from @t2
) b
    on a.col_a = b.col_a
    and a.col_b = b.col_b
    and a.rown = b.rown
order by isnull(a.col_a,b.col_a),isnull(a.rown,b.rown)

Using row_number as part of a full join is what allows for the null values to be created.

3

solved How do I join on multiple columns in SQL Server and include columns in one table that aren’t present in other tables?