Re: where clauses and multiple tables

On 09/09/2009, at 9:02 AM, David W Noon wrote:

> On Tue, 8 Sep 2009 14:25:20 -0700, Scott Frankel wrote about [GENERAL]
> where clauses and multiple tables:
>
>> Is it possible to join tables in the where clause of a statement?
> [snip]
>> Given a statement as follows:
>>
>> SELECT foo.foo_id, foo.name
>> FROM foo, bar
>> WHERE foo.bar_id = bar.bar_id
>> AND bar.name = 'martini';
>
> Just use an IN predicate:
>
> SELECT foo_id, name FROM foo
> WHERE bar_id IN (SELECT bar_id FROM bar WHERE name = 'martini');
>
> This is frequently called a semi-join.

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';

Thanks!

Yar