Hey Set!

>> [Query1]
>> select id from TEST where id2 in (select id2 from subquerytable);
>>
>> [Query2]
>> select id from TEST where id2 in (1,2);

> The subselect in query1 cannot benefit from any index since it doesn't have a
> WHERE clause. More importantly Query1 is not a good query to use in
> Firebird. The subselect is logically executed once for every record in TEST.
> This is done because similar queries like
>
> select id from TEST where id2 in (select id2 from subquerytable where
> TEST.Name = subquerytable.Name)
>
> would have to execute the subquery for every row in TEST.
>
> Rewrite your query to
>
> select id from TEST T where exists(select * from subquerytable sq where
> sq.id2 = t.id2)
>
> This query would use an index for subquerytable.

It is extraordinary for me to question the details in your posts, they are usually the 'gold standard'.

But in this case I think you are off the mark.


Without a filter on the TEST table which uses an indexed condition (or an ORDER BY using an index), the optimizer would always perform a NATURAL scan of the TEST table, since it must test each row for the presence of a match in the sub-query.

There is nothing in the query criteria which would cause the optimizer to use an index for the TEST table.


Take care

Sean