TSQL: NOT IN doesn’t work

If you encounter a weird scenario when query

SELECT Something FROM Table1 WHERE SomethingElse IN (SELECT Lookup FROM Table2)

work perfectly, but the opposite query

SELECT Something FROM Table1 WHERE SomethingElse NOT IN (SELECT Lookup FROM Table2)

doesn’t return any results – and you know for a fact that there’re results – check values returned by the subquery. Chances are there’re NULLs among those values. If that’s the case – NOT IN won’t return any results.

As a quick workaround you can add IS NOT NULL condition to the subquery:

SELECT Something FROM Table1
WHERE SomethingElse NOT IN (SELECT Lookup FROM Table2 WHERE Lookup IS NOT NULL)

and this should do the trick.

Credit: This Stack Overflow answer.

One reply

  1. I think this kind of thing is surprisingly common in SQL, where value + null = null.

    I presume that’s how it’s treating the subquery, concatenating with a null and giving you no value.

Leave a Reply

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