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.

6 comments:

Anonymous said...

Hi Kamsy .. 

Indeed it does help!!

cheers,

markpooj

Anonymous said...

Thanks alot, I was wondering why this is happaning. Now its clear to me.

Prakash

Anonymous said...

Oooho thats it, you saved my day pal thanks alot
Cheers
Daniel

Anonymous said...

thank you very much for sharing this. It really helps

Imikowa

shiran said...

You mean this ya?

SELECT customerName FROM customer
WHERE NOT EXISTS
(SELECT DISTINCT customerID FROM Invoice WHERE customer.Invoice= Invoice.Invoice)

shiran said...

Sorry there is an error on where clause in my above comment.

SELECT customerName FROM customer
WHERE NOT EXISTS
(SELECT DISTINCT customerID FROM Invoice WHERE customer.customerID= Invoice.customerID)

 
Blog Directory - OnToplist.com