How to apply an outer join to 2 tables using a staging table?

Question

table1 | id | value -------------------- 1 | john 2 | frank 3 | patel 4 | jim table2 | id | value -------------------- 6 | steve 7 | tim 8 | sunny 9 | bhaskar merged | tabid1 | tabid2 ------------------------------ 3 | 7 4 | 8 

Is required

:

  output | tabid1 | tabval1 | tabid2 | tabval2 1 | john | NULL | NULL 2 | frank | NULL | NULL 3 | patel | 7 | tim 4 | jim | 8 | sunny NULL | NULL | 6 | steve NULL | NULL | 9 | bhaskar 

I tried:

 SELECT * FROM table1 LEFT JOIN merged m1 ON table1.id = m1.tabid1, merged m2 RIGHT JOIN table2 ON table2.id = m2.tabid2 

But giving it 16 lines instead of the required 6

Answer 1

 select table1.id,table1.value,table2.id,table2.value from table1 left join merged on table1.id=merged.tabid1 left join table2 on merged.tabid2=table2.id union select table1.id,table1.value,table2.id,table2.value from table2 left join merged on table2.id=merged.tabid2 left join table1 on merged.tabid1=table1.id ; 

or with the right to join:

 SELECT * FROM table1 LEFT JOIN merged on table1.id=merged.tabid1 LEFT JOIN table2 ON merged.tabid2=table2.id UNION SELECT * FROM table1 RIGHT JOIN merged on table1.id=merged.tabid1 RIGHT JOIN table2 ON merged.tabid2=table2.id WHERE table1.id IS NULL; 

Answer 2

 SELECT table1.id tabid1, table1.value tabvalue1, table2.id tabid2, table2.value tabvalue2 FROM merged FULL OUTER JOIN table1 ON merged.tabid1 = table1.id; FULL OUTER JOIN table2 ON merged.tabid2 = table2.id; 

Answer 3

 SELECT table1.id AS tabid1, table1.value AS tabval1, table2.id AS tabid2, table2.value AS tabval2 FROM table1 LEFT JOIN merged m1 ON table1.id = m1.tabid1 RIGHT OUTER JOIN table2 ON table2.id = m1.tabid2 

I do not know why you felt the need to join merged twice there.