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'.
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
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
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
DDI: 01323 488548
Mobile: 0773 671 5772
In response to
pgsql-novice by date
|Next:||From: Leo||Date: 2008-02-19 14:34:20|
|Subject: Re: Full text indexing of documents|
|Previous:||From: Tom Lane||Date: 2008-02-19 03:58:54|
|Subject: Re: SQL question |