Hi,

> > Raymond, let me answer in descending order.
> > Why. Because FB serves IN (Select ...) scanning table in main select
> > and performing subquery. Seemingly, MS SQL perform subselect once and
> > perhaps use indices on main table if it is possible.
>
> Can someone explain why FB performs the sub-select for each row in the

main

> select even if the sub-select is not correlated and will always return the
> same resultset ?

Internally the IN is converted to the same as a EXISTS function :

FieldX IN (SELECT FieldY FROM TableA)

becomes :

EXISTS(SELECT FieldY FROM TableA WHERE FieldY = FieldX)

Changing this has been planned for a future version.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info