"Svein Erling Tysvжr" <svein.erling.tysvaer@...> wrote:

>
> Well, I do not know whether this is completely correct, but the
> general performance of using IN <subselect> has made me avoid it
> completely. Generally, I use
>
> where exists(select * from table2 where table2.field2 = table1.field1)
>
> rather than
>
> where table1.field1 in (select field2 from table2)
>
> This avoids the need of selecting every record from table2 for each
> record in table1.

Both ways will read only one (corresponding) record from table2 per
iteration if field2 is indexed and both will read the entire table2
otherwise. IN is much slower only when aggregation is performed inside a
subselect and this is improved in v2.0. Aside from this, I don't recall any
differences between IN and EXISTS.


Dmitry