Re: SQL question

From: Steve T <steve(at)retsol(dot)co(dot)uk>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL question
Date: 2008-02-19 07:23:18
Message-ID: 1203405799.16875.235.camel@retsol610
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I was lead to believe that the benefit of JOINing tables was that you
could explicitly state the criteria to be used - and hence the 'index'.
So
SELECT a.blah
FROM table1 AS a
JOIN table2 AS b
JOIN table3 AS c
ON c.foreignRecNo = b.recNo
ON b.foreignRecNo = a.recNo

explicitly says use the foreign key rec nos on table2 and table3 - where
as

SELECT a.blah
FROM table1 AS a, table2 AS b, table3 AS c
WHERE b.foreignRecNo = a.recNo
AND c.foreignRecNo = b.recNo

would leave it up to the optimiser to decide the best route to the data.

Personally, since realising how JOIN works (and the JOIN syntax), I find
that layout far easier to read as well - especially where you get
convoluted where clauses such as:

WHERE table1.field1 = table3.field4
AND table2.field10 = table4.field2
AND table2.field7 = table4.field3
AND table2.field5 = table5.field1
AND table .....

where the 'joins' are on composite keys and the order of specification
of the 'join' isn't necessarily intuitive.

On Tue, 2008-02-19 at 14:49 +1100, Phillip Smith wrote:

> De: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] En nombre de Mag Gam
> Enviado el: Monday, February 18, 2008 5:37 PM
> Para: pgsql-novice(at)postgresql(dot)org
> Asunto: [NOVICE] SQL question
>
> >> Is it better to use "JOIN" or comma method with equal sign when doing
> table joins?
>
> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Cesar Alvarez
> Sent: Tuesday, 19 February 2008 14:21
> To: pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] SQL question
>
> > It's better to use Join cause its easier to read the Sql statement
>
>
> I'm guessing there might be some effect on the planner as well maybe...?
> Someone smarter than me could shed some more light...
>
>
> THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
>
> *******************Confidentiality and Privilege Notice*******************
>
> The material contained in this message is privileged and confidential to
> the addressee. If you are not the addressee indicated in this message or
> responsible for delivery of the message to such person, you may not copy
> or deliver this message to anyone, and you should destroy it and kindly
> notify the sender by reply email.
>
> Information in this message that does not relate to the official business
> of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
> Weatherbeeta, its employees, contractors or associates shall not be liable
> for direct, indirect or consequential loss arising from transmission of this
> message or any attachments
> e-mail.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Steve Tucknott
ReTSol Ltd

DDI: 01323 488548
Mobile: 0773 671 5772

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Leo 2008-02-19 14:34:20 Re: Full text indexing of documents
Previous Message Tom Lane 2008-02-19 03:58:54 Re: SQL question