SQL left join with filter in JOIN condition vs filter in WHERE clause

Question

I'm refactoring some sql at work, and stumbled on something I'm not sure how to explain. There are two queries I thought would result in the same result, but don't, and I'm not sure why.

The queries are as follows:

select *
from TableA as a
left join TableB b on a.id = b.id and b.status in (10, 100)

select *
from TableA as a
left join TableB b on a.id = b.id
where b.status is null or b.status in (10, 100)

When will these not return the same result?

Answer 1

The big difference with the Where condition b.status is null or b.status in (10, 100) is when b.status is say 1 as well as b.id=a.id

In the first query you will still get the row from table A with corresponding B part as NULL as On condition is not fully satisfied. In the second query you will get the row in the JOIN for both a and b tables which will be lost in the where clause.