[SOLVED] Merge multiple tables with a common column name

Merge multiple tables with a common column name

I am trying to merge multiple tables that have a common column name which need not have the same values across the tables. For ex,

    -tmp1-
    id dat
    1  234
    2  432
    3  412

    -tmp2-
    id nom
    1  jim
    2  
    3  ryan
    4  jack

    -tmp3-
    id pin
    1  gi23
    2  x4ed
    3  yit42
    8  hiu11

If above are the input, the output needs to be,

    id  dat  nom  pin
    1   234  jim  gi23
    2   432       x4ed
    3   412  ryan yit42
    4        jack
    8             hiu11

Thanks in advance.

postgresql 8.2.15 on greenplum from R(pass-through queries)

Solution

use FULL JOIN ... USING (id) syntax. please see example: http://sqlfiddle.com/#!12/3aff2/1

this is how diffrent join types work (provided that tab1.row3 meets joining condition with tab2.row1, and tab1.row3 meets tab2.row2):

| tab1 | | tab2 | |         JOIN          | |       LEFT JOIN       | |       RIGHT JOIN      | |       FULL JOIN       |
-------- -------- ------------------------- ------------------------- ------------------------- -------------------------
| row1 |                                    | tab1.row1 |                                       | tab1.row1 | 
| row2 |                                    | tab1.row2 |                                       | tab1.row2 |
| row3 | | row1 | | tab1.row3 | tab2.row1 | | tab1.row3 | tab2.row1 | | tab1.row3 | tab2.row1 | | tab1.row3 | tab2.row1 |
| row4 | | row2 | | tab1.row4 | tab2.row2 | | tab1.row4 | tab2.row2 | | tab1.row4 | tab2.row2 | | tab1.row4 | tab2.row2 |     
         | row3 |                                                                 | tab2.row3 |             | tab2.row3 |
         | row4 |                                                                 | tab2.row4 |             | tab2.row4 |