Svein Erling Tysvær wrote:

>
> One - unfortunately - common way to use IN, is to use IN(SELECT ...), which can never use indexes and used to execute the subselect for every possible row (needless to say, this was S L O W when the subselect returned lots of rows). I think later versions of Firebird try to transform such a construct into WHERE EXISTS(...), but I doubt that is possible in all cases (haven't thought too much about it, but it sounds difficult to cover all theoretical cases).

It was always executed in the manner similar to the EXIST predicate. The
recent FB versions just cover more possible cases, like an embedded
(into a sub-query) GROUP BY clause or UNION. For such a transformation
being possible, IN is internally made correlated, so that:

<value> IN ( SELECT <field> FROM <tab> WHERE <cond> )
becomes a kind of:
EXISTS ( SELECT * FROM <tab> WHERE <cond> AND <field> = <value> )

And, if the injected equality predicate can be matched to an available
index, the subquery becomes indexed and it's executed similar to an
inner join but with a fixed join order (outer select -> subselect). So,
generally, IN always use indices, but it's done for the inner stream --
not for the outer one as many people expect it to. This is not
necessarily bad, although sometimes it is.


Dmitry