At 09:30 PM 10/03/2005 -0500, you wrote:

>:: The optimizer resolves IN <subquery> predicates to EXISTS
>:: <set> anyway.
>
>So IN = EXISTS? Or are they processed differently in any way?

In Firebird, not really, since all existential queries are massaged into
EXISTS() expressions if at all possible.

>Is there a reason if the subquery is static (ie no fields referneced from
>outer) that FB does not run it once and cache the result set?

I believe that is what happens with a non-correlated subquery.

>:: >What if I
>:: >just did a EXISTS not not a NOT EXISTS? Any diff?
>::
>:: Yes. Different result by 180 degrees.
>
>Why does not create such a difference for FB? Is it because it has to scan,
>vs using some kind of index?

Not clear what question you were asking. In case you meant to ask "How
does EXISTS() decide?" then the answer is that EXISTS() reads the table
until it finds the first match and exits immediately. Logically, it is an
OR-ed query with shortcut resolution, i.e. the answer is true when the
first row tested returns a match. It will come back with False if it
processes the whole table and finds no match. So, often, you will be able
to write an EXISTS() query that uses a descending index if there's a chance
that the first "found" row occurs closer to the right-hand extremity of the
(ascending) index than to the beginning of the index.

NOT EXISTS(), on the other hand, has to process the whole table *every*
time, since it has no other way to establish the truth or otherwise of the
predicate. Logically, it is an AND-ed predicate, i.e. all rows have to
have to match the "<>" criterion.

Still don't know whether this is the question you were asking, though.

./hb