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

Re: Left Outer Join Syntax

From: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Left Outer Join Syntax
Date: 2004-08-14 16:57:45
Message-ID: 1092502667.1255.63.camel@retsol1 (view raw or flat)
Thread:
Lists: pgsql-novice
Tom,
Thanks for that.
I'll be testing the converted system thoroughly, so should pick up all
the anomalies that I've introduced! 
I can now finish off some of the more obscure joins in the code before I
start the data import and then testing.

On Sat, 2004-08-14 at 16:44, Tom Lane wrote:

    Steve Tucknott <steve(at)retsol(dot)co(dot)uk> writes:
    > How do I include the join of table F to table D where F.colD = D.colF in
    > the case where 1) F is a LEFT OUTER and 2) where F is plain (INNER?)
    > join   
    
    I think you just want to parenthesize the join constructs:
    
    	(a left join (f left join d on somecondition) on somecondition)
    or
    	(a left join (f join d on somecondition) on somecondition)
    
    However you need to be clear in your mind about the semantic behavior
    you want before you can pick a join order, and your question certainly
    didn't give enough detail for anyone to offer advice.  In either one of
    the above examples, D rows that don't have a join partner in F will
    disappear before they get to the A join, resulting in different results
    than you had before --- that is, some A rows that were joined to D rows
    would now be extended with with nulls.  If any of those rows make it to
    the final output then you will see a different and probably less useful
    answer.
    
    The short form of my point is that outer joins aren't associative and so
    the order in which you do them matters a lot.  The reason JOIN is
    syntactically like an operator is so that you can control that ordering
    through parentheses.
    
    			regards, tom lane
    
    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?
    
                   http://archives.postgresql.org



Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

In response to

pgsql-novice by date

Next:From: operationsengineer1Date: 2004-08-14 21:04:30
Subject: Re: PGSQL 8-beta For WinXP Home Edition Instructions
Previous:From: Tom LaneDate: 2004-08-14 15:44:24
Subject: Re: Left Outer Join Syntax

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