I'm trying to replace not exist in with left join, but my query is not returning the same result

Question

so I'm trying to using left join instead using not exist (which I always disliked), but somehow I'm not getting the same result.

Original query that returns the correct result:

select o.number, o.facility code, o.patient_id,o.shipdate, o.ordercreationstatus
from orders o
where order.status in (1,2,3)
and 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 ;

Here is the query version using 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
where order.status in (1,2,3)
and os.keyid = o.number
and os.status not in (1,2,3,5)
and o.ordercreationstatus is null;

When I ran my second query, a lot more rows are returning. I think it's because logic of my query is wrong?

and os.status not in (1,2,3,5), this part since the original nest query is using not exist, this should not be in my and condition?

Answer 1

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)
WHERE 
    o.status IN (1, 2, 3)
    AND o.ordercreationstatus IS NULL
    AND os.keyid IS NULL
ORDER BY 
    o.shipdate, 
    o.ordercreationstatus;

You want the left join to match on the key AND the 4 statuses that are in scope. Under this condition if no matching row is found, you'll get a null keyid on the OrderStage table.

Knowing that you can just filter out any results that have a null os.keyid