SQL查询：用LEFT JOIN替换NOT EXISTS后结果不一致，求修正

问题背景 #

尝试用LEFT JOIN替代NOT EXISTS改写SQL查询，但结果与原查询不一致，改写后的查询返回了更多行，疑惑是否是os.status not in (1,2,3,5)这部分的问题。

原查询（结果正确） #

注：原查询中order.status应为o.status，属于笔误，已修正

select o.number, o.facility code, o.patient_id,o.shipdate, o.ordercreationstatus
from orders o
where o.status in (1,2,3)
and o.ordercreationstatus is null
and not exists  
                (select 1 
                 from orderstage os
                 where os.keyid = o.number
                 and os.status in (1,2,3,5))
order by o.shipdate, o.ordercreationstatus ;

错误的改写查询 #

注：同样修正order.status为o.status的笔误

select o.number, o.facility code, o.patient_id,o.shipdate, o.ordercreationstatus
from orders O
left join orderstage os on os.keyid = o.number
where o.status in (1,2,3)
and os.keyid = o.number
and os.status not in (1,2,3,5)
and o.ordercreationstatus is null;

问题分析与正确改写 #

错误原因 #

原NOT EXISTS的逻辑是：当前订单o不存在任何一条orderstage记录，满足os.keyid = o.number且os.status在(1,2,3,5)。

你的改写逻辑完全偏离：

1. left join后在WHERE子句加os.keyid = o.number，直接把左连接变成了内连接，只能返回有对应orderstage记录的订单；
2. os.status not in (1,2,3,5)筛选的是「存在对应orderstage且状态不在目标范围」的订单，这和原逻辑（不存在任何符合状态的orderstage）完全相反，所以会返回更多行。

正确的LEFT JOIN等价写法 #

select o.number, o.facility code, o.patient_id,o.shipdate, o.ordercreationstatus
from orders o
left join orderstage os 
  on os.keyid = o.number 
  and os.status in (1,2,3,5) -- 将状态筛选条件放到JOIN的ON子句中
where o.status in (1,2,3)
and o.ordercreationstatus is null
and os.keyid is null -- 表示没有匹配到符合条件的orderstage记录
order by o.shipdate, o.ordercreationstatus ;

逻辑解释 #

• 把os.status in (1,2,3,5)放到LEFT JOIN的ON条件里，只会尝试匹配符合该状态的orderstage记录；
• 最后筛选os.keyid is null，就表示当前订单o没有任何符合条件的orderstage记录，和原NOT EXISTS的逻辑完全一致。

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