query fails when "not in" is used

[vk]

Hi

I am having a problem with access 2000 sql query not executing
properly on a particular table. It's a pretty simple query that lists
rows present in one of two tables.

select id from a
where id not in (select id from b)

As a result of this I get a single blank row returned. When i reverse
"a" and "b" the query works. If I replace "b" with another table or
even an older version of the same table it works. I think that there
is some sort of table corruption, but hope that it's a bug that has
been fixed. I tried compact / repair and it didn't change anything. I
get no error messages and don't know where to find an error log or
even if it exists.

Any help is greatly appreciated.

vk

 

[Van T. Dinh]

It is also possible that every Record in Table "a" exists in Table "b" and
in this case the query *correctly* returns and empty result set.

BTW, "Not In" is not efficient and you can replace it with a Left Join like:

SELECT [id]
FROM a
LEFT JOIN b
ON a.[id] = b.[id]
WHERE b.[id] Is Null

 

[Michel Walsh]

Hi,


Not necessary a corruption, but a Null. Have you a NULL among the ID
values?


If so, what is " IN " means? If I say:

x IN ( 1, 2, 3)

I really meant:

x = 1 OR x=2 OR x=3


Now, Not IN meant the negation of that (deMorgan's law):


x<>1 AND x<>2 AND x<>3


Now, assume we have not 3, but NULL:


x<>1 AND x<>2 AND x<>NULL


So, if 5 = x, we literally got:

5<>1 AND 5 <>2 AND 5<>NULL

which is computed to

True AND True AND NULL

which is

NULL


So, it is not TRUE, it is unknown, it is null, that 5 NOT IN( SELECT
field FROM somewhere) if the field has a record with a NULL value in it.
And that holds for all records, so, no record are returned!



In other words, if the field has a null, nothing is to be returned from
the

WHERE someField NOT IN( SELECT field FROM somewhere )


and it is a standard behavior, not a bug, given the definitions of NULL,
IN and NOT.


Now, if you do not believe me on my words (and you should never believe me
on my words), you may "test" the following:


SELECT *
FROM myTable
WHERE f1 NOT IN( 1, 2, Null)


and you will get results! Is that Vanderghast completely out of mind?
Maybe, but not in this case... It is that JET evaluates differently NOT IN
(constant list) and NOT IN (SELECT field FROM somewhere). The two
expressions behave differently when a NULL is involved. After all, who, with
a sane mind, would type

WHERE fieldName NOT IN(1, 2, NULL)

when it is logically equivalent to

WHERE Null

In that case, in the case of a constant list, Jet tries to help to make
sense (that is arguable, but that is the way it behaves anyhow) and, for a
constant list, removes the NULL.




Hoping it may help,
Vanderghast, Access MVP