Using NOT IN is fraught with problems because of the way SQL handles NULLs. As a general rule, do not use NOT IN unless you are certain that the result set will not include a NULL.
Let’s see an example below. In this example, we want to query all department records from departments table except these four 10, 20, 30 and 40. And it works just fine.
But if you add a NULL to this condition, you won’t get any results at all despite that departments table does not contain a NULL value either
It looks a little strange, and this is why I dedicated a whole post about it. But this is how SQL handles NULL. You just need to remember it and avoid using NOT IN and NULL together.
IN and NULL
At the same time, IN and NULL works, but NULL values are not returned
As we can see all departments from the IN list were shown, except the NULL department