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