Merging many tables

Question

Suppose there are the following three PostgreSQL tables to be merged with the common ID column.

table A  
ID V1
2  100
3  200
4  250

table B
ID V2
1  200
3  140

table C
ID V3
2  90
3  100
4  10
5  200

I would like to merge these three tables as follows:

merged table
ID V1   V2   V3
1       200  
2  100       90
3  200  140  100
4  250       10
5            200

I appreciate your help!

score 1 · Answer 1 · answered Mar 18 '13 at 20:33

1

    select A.*,B.V2,C.V3
    from 
    A 
    full outer join B on A.id = B.id
    full outer join C on C.id = coalesce(A.id,B.id)

answered Mar 18 '13 at 20:33

alex

Note - I haven't tested this. – alex Mar 18 '13 at 20:57
Doesn't quite work as is. It needs `COALESCE()` for the `id`. – Erwin Brandstetter Mar 18 '13 at 21:41

score 1 · Accepted Answer · answered Mar 18 '13 at 21:40

Since you want the id column only once in the result, the obvious choice would be an equijoin with the USING clause:

SELECT id, v1, v2, v3
FROM   a 
FULL OUTER JOIN b USING (id)
FULL OUTER JOIN c USING (id)

This way you also don't need COALESCE at all - which gets messy with lots of tables quickly.

2 Answers2