Combining two tables with some matching IDs but not all-CodePudding

So I have two tables. Both tables have some matching IDs but not all IDs are in each table. I need to be able to combine the two. Example down below:

Table 1

Name      Money    ID
Bob       500      1
Nick      600      2
Kim       575      3

Table 2

Name      Fees    ID
Bob       200      1
Nick      250      2
Ian       100      4

OUTPUT

Name    Money   Fees  ID
Bob     500     200   1
Nick    600     250   2
Kim     575           3
Ian             100   4

CodePudding user response：

This is a simple full outer join with some coalesce to keep the values in a single column:

select coalesce(tbl1.[Name], tbl2.[Name]) [Name]
    , tbl1.[Money]
    , tbl2.Fees
    , coalesce(tbl1.ID, tbl2.ID) ID
from tbl1
full outer join tbl2
    on tbl1.ID = tbl2.ID
order by ID