如何使用SQL FULL JOIN关联5个含独立ID列的表以获得预期结果？

现有5张不同的表，分别以col1、col3、col5、col7、col9作为ID列，表结构及数据如下：

Table 1        Table 2     Table 3     Table 4     Table 5 

col1 col2      col3 col4   col5 col6   col7 col8   col9 col10
 a    5         a    7      c    6      a    5      b     6
 b    7         b    8      f    2      c    6      c     7
 c    2         c    3      g    5      f    9      e     1
 e    5         d    7      h    8      g    3      f     5
 f    9         f    5      j    5      i    4      i     8
 g    3         g    1      k    2      k    6      
 h    6         h    9      l    6      n    1      
 j    1         i    7      n    8              
 k    5         j    4      o    3              
 m    6         k    7                       
 o    2         l    2                       
                n    5                  

需求是将所有表进行关联，无论ID是否匹配，得到如下预期结果：

col1 col2 col4 col6 col8 col10
 a    5    7         5  
 b    7    8              6
 c    2    3    6    6    7
 d         7            
 e    5                   1
 f    9    5    2    9    5
 g    3    1    5    3  
 h    6    9    8       
 i    1    7         4    8
 j    5    4    5       
 k         7    2    6  
 l         2    6       
 m    6              
 n         5    8    1  
 o    2         3       

尝试了如下SQL语句，但发现Table2、Table3、Table4、Table5的ID未正确关联：

SELECT table1.col1, table1.col2, table2.col4, table3.col6, table4.col8, table5.col10
FROM table1
FULL JOIN table2 ON table1.col1 = table2.col3
FULL JOIN table3 ON table1.col1 = table3.col5
FULL JOIN table4 ON table1.col1 = table4.col7
FULL JOIN table5 ON table1.col1 = table5.col9;

问题原因 #

原SQL的问题在于，每次FULL JOIN都基于table1.col1做关联条件。当其他表中存在table1没有的ID（比如d、k、l等）时，后续连接的条件table1.col1 = xxx会因为table1.col1为NULL而无法匹配，导致这些ID的其他表字段无法正确显示。

正确解决方案 #

先收集所有表中的唯一ID，形成一个全量ID集合，再以此为基础左连接所有表，确保每个ID都能对应到各表的相关字段。

具体SQL代码如下：

WITH all_ids AS (
    SELECT col1 AS id FROM table1
    UNION
    SELECT col3 AS id FROM table2
    UNION
    SELECT col5 AS id FROM table3
    UNION
    SELECT col7 AS id FROM table4
    UNION
    SELECT col9 AS id FROM table5
)
SELECT 
    ai.id AS col1,
    t1.col2,
    t2.col4,
    t3.col6,
    t4.col8,
    t5.col10
FROM all_ids ai
LEFT JOIN table1 t1 ON ai.id = t1.col1
LEFT JOIN table2 t2 ON ai.id = t2.col3
LEFT JOIN table3 t3 ON ai.id = t3.col5
LEFT JOIN table4 t4 ON ai.id = t4.col7
LEFT JOIN table5 t5 ON ai.id = t5.col9
ORDER BY ai.id;

代码说明 #

• 使用UNION而非UNION ALL：自动去重，得到所有唯一的ID值；
• 全量ID集合作为主表：保证所有表中出现过的ID都被包含；
• 左连接各原始表：每个ID都能匹配到对应表的字段，不存在的字段自然显示为NULL，符合预期结果；
• ORDER BY ai.id：使结果ID顺序与预期一致。

内容的提问来源于stack exchange，提问作者izzatfi