PostgreSQL中如何合并两张表得到指定联合结果

问题描述 #

现有两张表，结构及数据如下：

table1 #

select tt1.*
from 
(values(1,'key1',5),(2,'key1',6),(3,'key1',7)
) as tt1(id, field2,field3)

对应数据：

table2 #

select tt2.*
from 
(values(4,'key1',null),(2,'key1',null),(3,'key1',null)) as tt2(id, field2,field3)

对应数据：

需要合并这两张表，得到如下目标结果：

select tt2.*
from 
(values(1,'key1',5),(2,'key1',6),(3,'key1',7),(4,'key1',null)) as tt2(id,field2,field3)

目标数据：

解决方案 #

以下两种方案均可实现需求，可根据实际场景选择：

方案1：通过FULL JOIN优先保留table1数据 #

SELECT 
    COALESCE(t1.id, t2.id) AS id,
    COALESCE(t1.field2, t2.field2) AS field2,
    COALESCE(t1.field3, t2.field3) AS field3
FROM table1 t1
FULL JOIN table2 t2 ON t1.id = t2.id;

逻辑说明：

• 用FULL JOIN关联两张表的id字段，确保所有id都被覆盖
• COALESCE函数优先取table1的字段值，当table1无对应id时（如id=4），自动取table2的字段值

方案2：通过UNION ALL+子查询去重合并 #

SELECT id, field2, field3 FROM table1
UNION ALL
SELECT id, field2, field3 FROM table2
WHERE id NOT IN (SELECT id FROM table1);

逻辑说明：

• 先取出table1的全部数据
• 再筛选出table2中未在table1出现过的id对应数据，将两部分结果合并

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