Thread: join and where clause equivalent ?

Hi all,

is

"SELECT * FROM a JOIN b USING (id_a)"

fully equivalent to

"SELECT * FROM a, b WHERE a.id_a=b.id_a" ?

Which is faster ?

TIA
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Nabil Sayegh wrote:

>Hi all,
>
>is
>
>"SELECT * FROM a JOIN b USING (id_a)"
>
>fully equivalent to
>
>"SELECT * FROM a, b WHERE a.id_a=b.id_a" ?
>
>Which is faster ?
>
>TIA
>
>
I think, the latter form gives the optimizer more freedom in choosing
the query path.
In terms of the results, the two are equivalent, as for which is faster
depends on how much you trust the optimizer - if you know exactly why a
should be the outer table in the join, then the first form is better, if
you want to leave the decision to the optimizer, then the second from is
for you.

Dmitry Tkach <dmitry@openratings.com> writes:
> Nabil Sayegh wrote:
>> is
>> "SELECT * FROM a JOIN b USING (id_a)"
>> fully equivalent to
>> "SELECT * FROM a, b WHERE a.id_a=b.id_a" ?

> I think, the latter form gives the optimizer more freedom in choosing
> the query path.

In this case they are completely equivalent.  If you had more than two
tables (ie, nested JOIN constructs) then there'd be a difference.  See
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=explicit-joins.html

            regards, tom lane