Union three tables where not all columns exist-CodePudding

I thought this would be a fairly straight-forward process but I am having issues unioning/merging three tables together. There are about 20 or so attributes for each prvs_ and cur_ prefix but I chose to just add a few for the question.

Table 1, #no_change_or_transfer looks like this with around 1 million rows:

prvs_loan_num  | prvs_wac  | prvs_tni  | cur_loan_num  | cur_wac  | cur_tni
---------------|-----------|-----------|---------------|----------|---------
 86            | 2.3       | 2000      | 86            |  2.5     |  2000

Table 2, #left_in_aug looks like this with around 20,000 rows:

prvs_loan_num  | prvs_wac  | prvs_tni  
---------------|-----------|-----------
 10            | 3.1       | 1500

Table 3, #joined_in_sept looks like this with around 20,000 rows:

cur_loan_num   | cur_wac   | cur_tni  
---------------|-----------|-----------
 22            | 3.5       | 1700

What I would like is the table to look like this:

prvs_loan_num  | prvs_wac  | prvs_tni  | cur_loan_num  | cur_wac  | cur_tni
---------------|-----------|-----------|---------------|----------|---------
 86            | 2.3       | 2000      | 86            |  2.5     |  2000
 10            | 3.1       | 1500      | NULL          |  NULL    |  NULL
 NULL          | NULL      | NULL      | 22            |  3.5     |  1700

I have tried a full outer join like this but received an error saying that column names in each table must be unique:

SELECT              *
INTO                #month_over_month
FROM                #no_change_or_transfer a
FULL OUTER JOIN     #left_in_aug b
    ON              a.prvs_loan_number = b.prvs_loan_number
FULL OUTER JOIN     #joined_in_sept c
    ON              a.cur_loan_number = c.cur_loan_number

I also tried this and received an error saying that all queries using a UNION must have an equal number of expressions in their target lists:

SELECT      x.*
    INTO    #month_over_month
    FROM    (SELECT * FROM #no_change_or_transfer
             UNION
             SELECT * FROM #left_in_aug
             UNION
             SELECT * FROM #joined_in_sept) x

CodePudding user response：

All columns in a union must match in each query.

Specify the column names explicitely and supply null where necessary.

Note that the column names are determined by the first query only.

 select prvs_loan_num, prvs_wac, prvs_tni, cur_loan_num, cur_wac, cur_tni
 from #no_change_or_transfer
 union all
 select prvs_loan_num, prvs_wac, prvs_tni, null, null, null
 from #left_in_aug
 union all
 select null, null, null, cur_loan_num, cur_wac, cur_tni 
 from #joined_in_sept

CodePudding user response：

You can just union the tables using UNION ALL. For example:

select prvs_loan_num, prvs_wac, prvs_tni, cur_loan_num, cur_wac, cur_tni
from #no_change_or_transfer
union all
select prvs_loan_num, prvs_wac, prvs_tni, null, null, null
from #left_in_aug
union all
select null, null, null, cur_loan_num, cur_wac, cur_tni
from #joined_in_sept