NOT IN and NULL

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

Leave a comment

Your email address will not be published. Required fields are marked *