--- In firebird-support@yahoogroups.com, "Gary Benade" <gary@...> wrote:

>
> This was posted in IBO earlier by mistake. Sorry.
>
> Say I have a table TEMP containing the values 1,2,3,4
>
> SELECT * FROM LARGETABLE WHERE MAININDEXVALUE IN (1,2,3,4)
>
> SELECT * FROM LARGETABLE WHERE MAININDEXVALUE IN ( SELECT * FROM TEMP)
>
> Does the second statement get optimised to execute as efficiently as

the

> first, or is there a performance penalty?

It depends on what you mean by efficient and performance penalty. For
4 records, both methods will most likely be pretty close.

The first will only work if there are less than 1499 numbers in the IN
clause. The second wont execute at all because the subselect needs to
return a single field, not all fields.

The first will hit the index one for each value.
SELECT * FROM LARGETABLE WHERE MAININDEXVALUE BETWEEN 1 AND 4

Will be more efficient.

Perhaps an inner join between LARGETABLE and TEMP will perform even
better than the second option.

Adam