Combine multiples table to a new one

Question

I apologize, I am not sure how to word this question. It probably has been answered somewhere, but I couldn't find it if so.

I have three tables that I want to combine in one using a common id column, with empty rows here and there.

table1

 id | col1 
----|------ 
  1 | 23.4 
  3 | 13.4 
  4 |  7.6 

table2

 id | col2 
----|------ 
  1 | 12.2 
  2 | 17.1 

table3

 id | col3 
----|------ 
  1 | 22.2 
  2 | 14.1 
  5 | 10.8 

I want to create a fourth table table4:

 id | col1 | col2 | col3 
----|------|------|------ 
  1 | 23.4 | 12.2 | 22.2 
  2 | NULL | 17.2 | 14.1 
  3 | 13.4 | NULL | NULL 
  4 | 7.6  | NULL | NULL 
  5 | NULL | NULL | 10.8 

I'm stumped as to how to approach this problem honestly.

Answer 1

I think you are looking for a FULL OUTER JOIN:

SELECT COALESCE(a.id,b.id,c.id), COALESCE(a.col1,'NULL'), COALESCE(b.col2,'NULL'), COALESCE(c.col3,'NULL')
  FROM table1 a
  FULL OUTER JOIN table2 b ON a.id=b.id
  FULL OUTER JOIN table3 c ON b.id=c.id;

or, if you want blanks instead of strings:

SELECT COALESCE(a.id,b.id,c.id), a.col1, b.col2, c.col3
  FROM table1 a
  FULL OUTER JOIN table2 b ON a.id=b.id
  FULL OUTER JOIN table3 c ON b.id=c.id;