0

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.

1
  • You need to use full outer joins. Dec 4, 2019 at 7:01

1 Answer 1

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;
1
  • If an ID exists in table1 and table3 but not table2, you will end up having two rows with that ID in the output, because the table3 joining condition doesn't take that case into account. Instead of just b.id = c.id you could use something like COALESCE(a.id, b.id) = c.id, or maybe c.id IN (a.id, b.id).
    – Andriy M
    Dec 4, 2019 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.