|
|
Posted by Erland Sommarskog on 08/19/07 12:16
Chris.Cheney (Chris.CheneyXXNOSPAMXX@tesco.net) writes:
> Erland Sommarskog <esquel@sommarskog.se> wrote in
> news:Xns9990ED010F0E2Yazorman@127.0.0.1:
>
>> You may be used to using IN / NOT IN for this, but (NOT) IN is limited,
>> as you get problem if you have a multi-column key. Plus that NOT IN
>> can trap you if NULL values are involved.
>
> Erland,
>
> Could you elaborate on this or point me to an appropriate web page,
> please.
I assume that you mean the point on NULL values.
Consider the script below. There are two tables, and we want to find
customers that are not known to be from outside the European Union. If you
run it, you will notice that the two queries return different results;
the query with NOT EXISTS returns one more row.
Maybe not the best example, but it's quite common to see questions on
the newsgroups where people ask why their NOT IN query does not return the
expected result, and these issues are almost always resolved with using
NOT EXISTS. Since I almost never use NOT IN myself, I have not paid
attention to what queries they really write.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|