At 11:15 AM 7/05/2004 +0200, you wrote:

> > From: Alexander V.Nevsky
> > To: firebird-support@yahoogroups.com
>[..]
> > 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 ?

If you are talking about Firebird here, not IB, then IN (<select list>)
resolves to a WHERE EXISTS(), which does use an index, if there is one. It
doesn't form an IN() list and it doesn't do an item-by-item
comparison. That makes it very efficient for the kind of search IN()
should be used for (WHERE-limited sets of up to a few hundred members) and
potentially still slow if you apply it to a huge set.

OTOH, IB does form an IN() list from the subquery and doesn't use an index.

/heLen