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.