T-Sql NOT IN is not returning expected results in SQL Server

I ran into this problem several times and when ever I encountered this problem I used to change my query to avoid this. But today I had sometime to dig into the root of this issue. Here are the problem, cause and the solution;

Lets take a simple example where you have a Customer table and a Invoice table (this is my favourite scenario since a POS system was my first ever commercial project). Invoice table has a foreign key to Customer table. Let say you have come across a situation where you want to find out customers who doesn't have any Invoices against them. You would probably write the following T-SQL (There are few other ways you can write this, but I know most of the SQL coders prefer IN and NOT IN);

SELECT customerName FROM customer WHERE customerID NOT IN (SELECT DISTINCT customerID FROM Invoice);

This may return values and sometimes may not. This is something make you feel very unsettling on a busy day. The problem is, this will work as far as customerID column in the Invoice table has no null values. If any single NULL value is there this will not return anything. Just simple as that :) anyway if you remove the nulls from the sub query this will work without a problem.

SELECT customerNAME FROM customer WHERE customerID NOT IN (SELECT DISTINCT customerID FROM Invoice WHERE customerID IS NOT NULL);

Alternatively you can use NOT EXISTS which will handle NULL values in a smarter way. Hope this will help, any question please feel free to ask.