LEFT JOIN查询未分配job_id=2的客户端出现重复数据的解决方案

现有SQL的逻辑缺陷出在过滤维度错误：

• LEFT JOIN执行时会先把clients表所有记录和job_allocations表的匹配记录全量关联，一个客户如果有多条岗位分配记录，关联后会生成多行结果
• 后续WHERE条件job_id <> 2 OR job_id IS NULL是逐行判断，不是按客户整体维度判断
  以id=2的客户Jane Doe为例，她同时关联出两条分配记录：job_id=1、job_id=2，其中job_id=1的那行完全满足job_id <> 2的判断条件，所以会被错误返回，和她实际已分配job_id=2的事实冲突。

以下两种写法都可以准确返回结果，不会出现重复、误判问题：

方案1：LEFT JOIN提前限定关联条件（JOIN阶段过滤效率更高） #

核心逻辑是左连接时就只匹配job_id=2的分配记录，最后筛选关联不到对应记录的客户即可：

SELECT c.id, c.Fullname
FROM clients c
LEFT JOIN job_allocations ja
  ON c.id = ja.client_id
  AND ja.job_id = 2 -- 注意这个条件写在JOIN ON子句中，不要写在WHERE里
WHERE ja.id IS NULL;

方案2：NOT EXISTS子查询（语义更直观） #

直接按客户维度判断「不存在job_id=2的分配记录」，逻辑和业务需求完全对齐：

SELECT c.id, c.Fullname
FROM clients c
WHERE NOT EXISTS (
  SELECT 1
  FROM job_allocations ja
  WHERE ja.client_id = c.id
    AND ja.job_id = 2
);

以上两种SQL的执行结果一致，仅会返回未分配job_id=2的客户：id=4，Jere Gray，不会重复返回Jane Doe。

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