How can I join multiple tables with same primary key to one table with one row per key?

user_327301 0
Viewed 1024

What I have

I have multiple tables with a primary key key and a value. Each key might be present in all tables or only in a subset of tables.

Here are example tables:

Tab1:

key value
A 1
C 3

Tab2:

key value
A 10
D 10

Tab3:

key value
B 20
D 10

What I want

I want to join the tables in such a fashion, that I get one row for each existing key and one column for each table. If a key is not present in one of the tables, null should be assumed. So for the example above I'd like to get this result:

key tab1 tab2 tab3
A 1 10 NULL
B NULL NULL 20
C 3 NULL NULL
D NULL 10 10

What I have tried so far

Here is what I came up with, which works:

with tab1 as (
    select * from (values('A', 1), ('C', 3))x(key, value)
),
tab2 as (
    select * from (values('A', 10), ('D', 10))x(key, value)
),
tab3 as (
    select * from (values('B', 20), ('D', 10))x(key, value)
)
select
    coalesce(tab1.key, tab2.key, tab3.key) "key",
    tab1.value tab1,
    tab2.value tab2,
    tab3.value tab3
from tab1
full outer join tab2 on tab2.key = tab1.key
full outer join tab3 on tab3.key = coalesce(tab1.key, tab2.key)
order by coalesce(tab1.key, tab2.key, tab3.key)

The question

In the case above my query looks still quite OK, however in the real case I need to join 6 tables and the key is actually distributed across three columns. This leads to very long join statements. The statement for the sixth table looks like this:

full outer join tab6
    on tab6.key1 = coalesce(tab1.key1, tab2.key1, tab3.key1, tab4.key1, tab5.key1)
    and tab6.key2 = coalesce(tab1.key2, tab2.key2, tab3.key2, tab4.key2, tab5.key2)
    and tab6.key3 = coalesce(tab1.key3, tab2.key3, tab3.key3, tab4.key3, tab5.key3)

The explosion of copy&pasted coalesce-calls is somewhat disturbing from a code quality perspective.

Is there another, simpler way to get the result I want?

postgresql sql
3 Answers

Solution for 3 tables

WITH keys AS (
    SELECT key FROM tab1 UNION
    SELECT key FROM tab2 UNION
    SELECT key FROM tab3 
)
SELECT key, t1.value, t2.value, t3.value
FROM keys AS k
LEFT JOIN tab1 AS t1 USING (key)
LEFT JOIN tab2 AS t2 USING (key)
LEFT JOIN tab3 AS t3 USING (key)

For the next table, just add it in the CTE keys to get all the occurring keys from all tables. Then attach this table to this CTE to obtain the value.

For 10 tables, it is still reasonably compact and logical and above all, it is easy to add more tables:

WITH keys AS (
    SELECT key FROM tab1 UNION
    SELECT key FROM tab2 UNION
    SELECT key FROM tab3 UNION
    SELECT key FROM tab4 UNION
    SELECT key FROM tab5 UNION
    SELECT key FROM tab6 UNION
    SELECT key FROM tab7 UNION
    SELECT key FROM tab8 UNION
    SELECT key FROM tab9 UNION
    SELECT key FROM tab10  
)
SELECT 
    key, 
    t1.value, t2.value, t3.value, t4.value, t5.value, t6.value, t7.value, t8.value, t9.value, t10.value
FROM keys AS k
LEFT JOIN tab1 AS t1 USING (key)
LEFT JOIN tab2 AS t2 USING (key)
LEFT JOIN tab3 AS t3 USING (key)
LEFT JOIN tab4 AS t4 USING (key)
LEFT JOIN tab5 AS t5 USING (key)
LEFT JOIN tab6 AS t6 USING (key)
LEFT JOIN tab7 AS t7 USING (key)
LEFT JOIN tab8 AS t8 USING (key)
LEFT JOIN tab9 AS t9 USING (key)
LEFT JOIN tab10 AS t10 USING (key)

There is a fiddle: https://www.db-fiddle.com/f/9aa2wUtR2RbQaGKTtXrr1W/1

or create a View when you could get all keys and joins values, or use FULL JOIN (see Lukas's solution)

user_135412550

Use FULL JOIN .. USING

SELECT key, t1.value AS tab1, t2.value AS tab2, t3.value AS tab3
FROM t1
FULL JOIN t2 USING (key)
FULL JOIN t3 USING (key)

dbfiddle

user_5217990 
select key1, key2, key3,
       tab1.value as value1,
       tab2.value as value2,
       tab3.value as value3,
       tab4.value as value4,
       tab5.value as value5,
       tab6.value as value6
from tab1 full join
     tab2
     using (key1, key2, key3) full join
     tab3
     using (key1, key2, key3) full join
     tab4
     using (key1, key2, key3) full join
     tab5
     using (key1, key2, key3) full join
     tab6
     using (key1, key2, key3);

The using clause ignores NULLs for a key value if another value is available, so the complex coalesce() expressions are not needed. You can then refer to the key values -- without table aliases -- elsewhere in the query.

user_11440350
Related