Hi Paul!

At 12:23 18.03.2003 +0100, you wrote:

>Hi Set,
>
>I also came up with the EXISTS solution but after seeing Xavier's post
>I think the IN solution is the quicker one here, because it features
>an independent subselect, that only has to be executed once.
>
>The subselect in our EXISTS solution is not independent; it has to be
>executed for every record in table1. Worse, NOT EXISTS makes it
>necessary to walk through the entire table2 for every record in
>table1. At least that's what we prescribe; I don't know if it would be
>optimized.

In theory I agree with you, but I don't think the optimizer do! With tables
only containing 100 and 75 records, Xaviers solution may well be quick. But
currently (from all the mails on this list where people have complained
when their IN <subselect> have yielded lousy performance) I think Firebird
executes the subselect once for every potential row returned, i.e. using IN
will result in Firebird executing (SELECT MYFIELD1 FROM MYTABLE2) 100 times
obtaining 75 records for each row in MYTABLE1 in Michaels example.

Maybe this changes in Fb 1.5 (which I haven't tried), but so far I haven't
seen any example of IN (<subselect>) being equally fast as using [NOT]
EXISTS (which I generally find amazingly fast).

Set