Edit: just realized you already proposed most of the answer.

Yes, its because the logic of a not in is to check <> AND for each value.

In the meantime, the IN doesn't really 'work' either. It won't find the nulls, it just doesn't 'break' the other values.

Try this:

select 'yes'

where null in (1,null)