Helen Borrie wrote:

> At 07:39 AM 17/05/2005 +0200, you wrote:
>>Kjell Rilbe wrote:
>>>Just have to make sure I'm not going crazy. Please confirm that these
>>>two SQL statements should return the same result:
>>>
>>>select count(*)
>>>from F
>>>where not exists (
>>> select 1
>>> from T
>>> where T.ID = F.ID
>>> )
>>>
>>>select count(*)
>>>from F
>>>where ID not in (
>>> select ID
>>> from T
>>> )

Helen to the rescue as usual. Thanks!

Since F.ID is aprimary key it doesn't contain any nulls so I hadn't
really considered the effect of nulls. The problem is that T.ID does
contain nulls. If I do it like this, the correct count is returned:

select count(*)
from F
where ID not in (
select ID
from T
where ID is not null
)

On the other hand, this returns 0:

select count(*)
from F
where not (ID in (
select ID
from T
))

I assume this latter query will first evaluate to:

...where not (null)

which in turn evaluates to:

...where null

which in turn is forced to binary boolean:

...where false

SO if I read Helen's post correctly she wasn't quite right, but close
enough to solve the mystery. No bug - my SQL was wrong.

Thank you Helen!
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64