如何将具有相同主键的多个表连接到每个主键一行的一个表

Question

我拥有的

我有多个表，其中有一个主键key和一个值。每个键可能存在于所有表中，也可能仅存在于表的一个子集中。

以下是示例表：

表1:

键	值
A	1
C	3

使用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

3个表的解决方案

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)

对于下一个表，只需将其添加到CTEkeys中，即可从所有表中获取所有出现的键。然后将此表附加到此CTE以获得值。

对于10个表，它仍然相当紧凑和合乎逻辑，最重要的是，添加更多的表很容易：

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)

有一把小提琴：https://www.db-fiddle.com/f/9aa2wUtR2RbQaGKTtXrr1W/1

或者在可以获得所有键和联接值时创建一个视图，或者使用FULL JOIN(参见Lukas的解决方案(

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);

如果有其他值可用，则using子句会忽略键值的NULLs，因此不需要复杂的coalesce()表达式。然后，您可以在查询的其他地方引用键值，而不需要表别名。