Amazon Redshift多表关联：找出HR_master与User_master无匹配pfid的行

首先明确你的核心需求：基于user字段关联三个表，找出HR_master中某个user的pfid，在User_master里没有任何一条对应user的pfid与其匹配的行，同时展示该user在User_master的所有pfid、Roaster_master的pfid。

原查询的问题 #

你当前的查询：

select UM.email,UM.pfid as UMpfid, HRM.pfid, RM.pfid 
from user_master UM 
left join HR_master HRM on (HRM.email=UM.email) 
left join Roaster_master RM on (RM.email=UM.email) 
where UM.pfid != HRM.pfid

会错误返回reno的记录，原因是：reno在User_master里有两条记录（pfid=2和pfid=4），其中pfid=2和HR_master的pfid=4不等，这条记录会被筛选出来，但实际上reno存在一条匹配的pfid=4，不符合你要排除「存在至少一条匹配」用户的需求。

正确的查询方案 #

我们可以用NOT EXISTS来精准判断HR_master的(user, pfid)在User_master中没有任何匹配，同时对User_master的pfid进行聚合，把同一个user的所有pfid合并展示：

select 
    agg_UM.user,
    agg_UM.all_pfids as USM_pfid,
    HRM.pfid as HRM_pfid,
    RM.pfid as RM_pfid
from (
    -- 聚合User_master中每个user的所有pfid，用|分隔
    select 
        user,
        string_agg(pfid::text, '|') as all_pfids
    from User_master
    group by user
) agg_UM
join HR_master HRM on agg_UM.user = HRM.user
join Roaster_master RM on agg_UM.user = RM.user
-- 核心条件：HRM的pfid在User_master中完全没有对应匹配
where not exists (
    select 1 
    from User_master UM
    where UM.user = HRM.user 
      and UM.pfid = HRM.pfid
);

结果说明 #

这个查询的逻辑：

1. 先聚合User_master，把每个user的所有pfid用|连接成字符串，比如rosh对应5|8，john对应7；
2. 关联HR_master和Roaster_master，确保只保留三个表都存在的用户（如果需要保留HR_master中存在但其他表不存在的用户，可以改成left join）；
3. 通过NOT EXISTS筛选出HR_master的pfid在User_master中完全没有匹配的用户，这样就会排除reno（因为HRM的pfid=4在User_master中存在），只返回符合要求的rosh和john记录，和你预期的输出一致。

如果你的数据库不支持string_agg（比如MySQL），可以用GROUP_CONCAT替代聚合部分：

-- MySQL版本的聚合逻辑
select 
    user,
    group_concat(pfid separator '|') as all_pfids
from User_master
group by user

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