Thread: Left Outer Join Syntax

What is the correct syntax for the following.
I have table A joined to table B and C. I also have table A left outer joined to tables D and E
and in turn D is joined to F

SELECT A.*,B.*,C.*,D.*,E.*,F.*
FROM A
          LEFT OUTER JOIN D
          ON A.colD      = D.colA
          LEFT OUTER JOIN E
           ON A.colE      = E.colA,
         B,
         C
WHERE A.col1 = ?
AND     A.colB = B.colA
AND     A.colC = C.colA

(I know I can move the B and C table joins and make them explicit joins off A)

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  

Thanks in advance
   

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769

Steve Tucknott <steve@retsol.co.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

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@retsol.co.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