At 07:02 PM 8/08/2006, you wrote:

>Hi
>
>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?

They are two different things. In the first case, the column value
is compared directly with each member of the in() set in a series of
OR-ed (where acolumn = avalue) tests.

In the second, the engine has to query another table each time it
reads a row and form an output set, to which it applies the OR-ed
tests. But your example has invalid syntax anyhow. The embedded
SELECT predicate MUST return single-column output.

In Firebird (but not in InterBase, as far as I know) the "in (select
...) syntax is reparsed internally to a WHERE EXISTS() predicate,
which is much faster, since it doesn't form an output set and the
query terminates when the first "true" occurs.

But, either way, the embedded select is much less efficient than the
set of constants. In a lot of cases (perhaps even *most* cases) the
comparison constants are not available except by an embedded query,
so the performance difference is academic anyway.

Helen