Postgres中如何编写SQL Join查询获取ContactPhoneRelation指定条件未关联数据

原有SQL错误原因 #

你写的SQL存在两个核心问题：

1. 关联条件只匹配了ContactId字段，没有校验你要求的ContactId+PhoneId组合匹配逻辑
2. WHERE子句直接写gt.groupId = 1，会把LEFT JOIN后GroupTable无匹配的行全部过滤，相当于直接转为INNER JOIN，最终只能查到指定groupId下已存在的组合，和需求完全相反。

正确写法 #

写法1：NOT EXISTS（推荐，语义清晰性能优） #

SELECT * FROM ContactPhoneRelation cp
WHERE cp.ContactId = '123'
AND NOT EXISTS (
    SELECT 1 FROM GroupTable gt
    WHERE gt.groupId = 1 -- 此处替换为需要查询的groupId
      AND gt.ContactId = cp.ContactId
      AND gt.PhoneId = cp.PhoneId
);

逻辑完全匹配需求：筛选ContactPhoneRelation中ContactId为123，且在指定groupId下没有相同ContactId+PhoneId组合的记录。测试时groupId填1返回id为2的目标记录，填2返回空行，完全符合预期。

写法2：LEFT JOIN + 空值过滤 #

如果更习惯用JOIN写法，可以按如下方式调整：

SELECT cp.* FROM ContactPhoneRelation cp
LEFT JOIN GroupTable gt 
  ON gt.groupId = 1 -- groupId筛选要放到JOIN关联条件中
  AND gt.ContactId = cp.ContactId
  AND gt.PhoneId = cp.PhoneId
WHERE cp.ContactId = '123'
  AND gt.id IS NULL; -- 仅保留GroupTable无匹配的行

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