>> Fernando Medeiros wrote:
>>> where afield = id
>>> if the afield is indexed, the performance is better.
>>>
>>> where afield in (id)
>>> the performance is same (low) with field indexed or not.
>>>
>>> correct me if i am wrong.
>> Sorry, but the optimizer will use an index for IN - both
>> formats of IN - list of values and select statement.
>>

What I should have said is that for reasons having to do with
the handling of null values of afield (I think) NOT IN is
optimized differently - and badly. So IN does use indexes,
but NOT IN won't always - maybe never. If you can live with
the difference between NOT IN and NOT EXISTS, use the latter.


Cheers,

Ann