Odd issue with existence check in query 1

[steve4king]

//results in >22000 records;
select distinct serial_no from customer;

//results in <7000 records;
select distinct serial_no from customer_serial;

//results in ~5000;
select count(*) from customer where serial_no in (select serial_no from customer_serial);

//results in 0;
select count(*) from customer where serial_no not in (select serial_no from customer_serial);

I'm not understanding how the 4th query could have zero results given the numbers in the prior queries.. Must be something wrong with my syntax..

Any thoughts?

Thanks,

-Stephen

 

[markros]

Check this blog post explaining this problem well
Why you should never use IN/NOT IN in SQL

And a hint - I assume Customer_Serial table has NULLs in the serial_no field.

PluralSight Learning Library

 

[gmmastros]

What do you get for this???

select count(*) from customer where serial_no not in (select serial_no from customer_serial [!]Where serial_no Is Not Null[/!]);

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[steve4king]

Doh, I can't believe I ignored that. You're both spot-on.
Thanks!