At 07:39 AM 17/05/2005 +0200, you wrote:

>Perhaps I should add that what I want is to get all F records whose ID
>does not appear in table T (column T.ID).

Yup. But you are apparently doing your testing on a different DB engine,
not known for conformance with standards.

More comments inline:

>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
> > )
>--

Not necessarily, since they are not necessarily equivalent. The first is
performing an existential test that, for each row in F, will return True as
soon as the first matching row in F is found. NOT EXISTS() then immediately
returns false and that row of F is excluded from the outer count.

In this case, if F.ID is null, then no comparison can possibly return True,
since (null = null) and (null = any value) both return "unknown", which
resolves to false in Firebird's binary logic.

Semantically, the second query theoretically has the boot on the other
foot. The inner predicate would return True if F.ID were null and null
were in the set returned by the subquery. I don't think Firebird ever
analyses the in(subquery) predicate this way, to arrive at (null in (null,
..)); but in systems where null is treated as a blank-value, not as true
null, this semantic distinction makes a difference to the evaluation.

To make the second query semantically equivalent to the first and
(theoretically) reliable across different dbms implementations, you might
need to bracket the predication within the NOT:

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

It's my belief that Firebird would resolve your second query to this, in
any case, and further resolve that to the first query. Firebird forms IN()
sets only when the test list consists of constants. What another DBMS does
is a question of implementation and fidelity to math rules.

Test it for yourself, though, and don't be afraid to use brackets to
eliminate doubtful assumptions.

./heLen