"Leyne, Sean" wrote:

> All,
>
> I am reviewing a program which was originally created 10+ years ago.
> Some SQL statements which are executed are performing extremely poorly
> and I am trying to optimize them.
>
> Accordingly, I'd like some "2nd opinions" about the way I am restating
> the statements.
>
> The original statement reads:
>
> select avg(updates.col_decim)
> from updates
> where
> updates.col_key in
> (select updates.col_key
> from updates, hundred
> where hundred.col_key = updates.col_key
> and updates.col_decim > 980000000)
>
> My new statement reads:
>
> select avg(updates.col_decim)
> from updates
> join hundred ON hundred.col_key = updates.col_key
> where updates.col_decim > 980000000
>
>
> I can tell you that they both provide the same answer, for the
> database I'm working with.
>
> Does anyone think that the two statements ARE NOT equivalent?
>
>
> Sean
>

For this query - yes. But if you have more conditions in the where of the
outer query the more exact equivalent is:

select avg(updates.col_decim)
from updates
join hundred ON (hundred.col_key = updates.col_key) and (updates.col_decim >
980000000)


--
Bojidar Alexanrov
Kodar Ltd.
http://www.Kodar.net