"not in" clause doesn't work

Is there anything that would cause a "not in" clause to not work in SQL2000?
DETAIL: I have 2 simple tables called "People" and "PeopleImport". Each of
these tables has a "flxID" field of type varchar(10). There is a record in
the "PeopleImport" table with a "flxID" value of '12345' and no records in
the People table with that value (I deleted it). Running the following
query does not return any records but (I think) should:
select flxid from peopleimport where flxid not in (select flxid from people)
I have also tried the following diviations:
select flxid from peopleimport where convert(char(5), flxid) not in (select
convert(char(5), flxid) from people)
select flxid from peopleimport where rtrim(convert(char(5), flxid)) not in
(select rtim(convert(char(5), flxid)) from people)
I hope someone has some thoughts on this because I am out of ideas.
Schoo
On Thu, 23 Dec 2004 15:54:46 -0600, Schoo wrote:

>Is there anything that would cause a "not in" clause to not work in SQL2000?
Hi Schoo,
None that I'm aware of, but many people get tripped by the way NULL values
are treated in IN and NOT IN clauses. Remember that NULL is (to the
database) an indicator for "unknown value".
When you deal with NULLs, you start using three-valued logic: 'true' and
'false' get company of their little brother 'maybe'. A row is only
included in the query if the where clause evaluates to 'true'; it is
discarded on 'false' and on 'maybe'.
For IN, the "strange" behaviour doesn't cause apparently wrong results:
* Is the value 15 IN the list {14, 17} --> NO (false)
* Is the value 15 IN the list {15, unknown value, 17} --> YES (true)
* Is the value 15 IN the list {14, unknown value, 17} --> MAYBE
The row will only be included in the query in the second example.
For NOT IN, the same logic yields results that "look" unlogical:
* Is the value 15 NOT IN the list {14, 17} --> YES (true)
* Is the value 15 NOT IN the list {15, unknown value, 17} --> NO (false)
* Is the value 15 NOT IN the list {14, unknown value, 17} --> MAYBE
This time, only the first case will return a row. The NOT IN with a NULL
value in the subquery can only result in NO or MAYBE and will therefor
automatically exclude all rows from the result.
Two ways to fix it:
Recommended: use NOT EXISTS instead of NOT IN. I never use NOT IN, except
when testing against a fixed list of constants!
SELECT flxid
FROM peopleimport AS pi
WHERE NOT EXISTS (SELECT *
FROM people AS p
WHERE p.flxid = pi.flxid)
Quick fix (not recommended, as you'll keep running into this as long as
you have subqueries with NOT IN): include WHERE flxid IS NOT NULL in the
subquery.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Thank you for your input. You are correct, dealing with the nulls in this
way works. I will incorporate these changes in the future.
Scott
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mkhms0hfp9hjdrpbtsp9v85vkkb1ksrgt7@.4ax.com... [vbcol=seagreen]
> On Thu, 23 Dec 2004 15:54:46 -0600, Schoo wrote:
SQL2000?
> Hi Schoo,
> None that I'm aware of, but many people get tripped by the way NULL values
> are treated in IN and NOT IN clauses. Remember that NULL is (to the
> database) an indicator for "unknown value".
> When you deal with NULLs, you start using three-valued logic: 'true' and
> 'false' get company of their little brother 'maybe'. A row is only
> included in the query if the where clause evaluates to 'true'; it is
> discarded on 'false' and on 'maybe'.
> For IN, the "strange" behaviour doesn't cause apparently wrong results:
> * Is the value 15 IN the list {14, 17} --> NO (false)
> * Is the value 15 IN the list {15, unknown value, 17} --> YES (true)
> * Is the value 15 IN the list {14, unknown value, 17} --> MAYBE
> The row will only be included in the query in the second example.
> For NOT IN, the same logic yields results that "look" unlogical:
> * Is the value 15 NOT IN the list {14, 17} --> YES (true)
> * Is the value 15 NOT IN the list {15, unknown value, 17} --> NO (false)
> * Is the value 15 NOT IN the list {14, unknown value, 17} --> MAYBE
> This time, only the first case will return a row. The NOT IN with a NULL
> value in the subquery can only result in NO or MAYBE and will therefor
> automatically exclude all rows from the result.
> Two ways to fix it:
> Recommended: use NOT EXISTS instead of NOT IN. I never use NOT IN, except
> when testing against a fixed list of constants!
> SELECT flxid
> FROM peopleimport AS pi
> WHERE NOT EXISTS (SELECT *
> FROM people AS p
> WHERE p.flxid = pi.flxid)
> Quick fix (not recommended, as you'll keep running into this as long as
> you have subqueries with NOT IN): include WHERE flxid IS NOT NULL in the
> subquery.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)