>> 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.
>

Hi Adam

Thanks for the input. My examples were contrived, I will try and explain if
you will bear with me plz.

SELECT * FROM LARGETABLE WHERE MAININDEXVALUE IN (1,5,11,49)

SELECT * FROM LARGETABLE WHERE MAININDEXVALUE IN ( SELECT MAININDEXVALUE
FROM TEMP)

Assuming temp contains the same values as the first.

BETWEEN won't work because there are some values in that range that must be
excluded, but you couldn't possibly have known that thanks to my super clear
example :)

In my real world app the IN statement has a couple of hundred values and the
query to filter for the values hits the system quite hard, so I was
concerned that using that query in the IN clause would cause more system
stress than supplying a string value generated from one execution of that
same query.

I use the IN clause as part of a DELETE statement and I can't figure out how
to use joins to do that, or even if it will be possible at all. An example
would be appreciated.

Thanks again,
Gary