Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group