Re: where clauses and multiple tables

On Tue, 08 Sep 2009 18:50:49 -0700, John R Pierce wrote about Re:
[GENERAL] where clauses and multiple tables:

>Yaroslav Tykhiy wrote:
>> By the way, folks, do you think there may be performance gain or
>> loss from rewriting this with an explicit JOIN?  E.g.:
>>
>> SELECT DISTINCT foo.foo_id, foo.name FROM foo JOIN bar ON foo.bar_id
>> = bar.bar_id WHERE bar.name='martini';
>
>I would expect that to be more efficient as its the 'proper' SQL way
>of doing things,

Actually, since the "bar" table does not supply any of the result
columns, the IN predicate is a more idiomatic (or "proper") way of
coding the query.

>and the optimizer will do a better job on it,
>especially if foo.bar_id is a FK to bar.bar_id's primary key.

The optimizer *should* produce the same plan, either way.

>btw, can't this be written...
>
>    SELECT DISTINCT foo.foo_id, foo.name
>        FROM foo JOIN bar ON bar_id
>        WHERE bar.name='martini';

The DISTINCT qualifier potentially changes the semantics, so the
immediate answer is "No".
--
Regards,

Dave  [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================