Re: outer join question

On Aug 17, 4:45 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> "matt lewis" <m..._at_lewis.co.uk> wrote in message
>
> news:fa4cq0$g2c$1_at_registered.motzarella.org...
>
> > Hi, I'm studying a book which is full of errors and the errata seems
> > sketchy.
>
> > Is this statement correct?
>
> > "In outer join statements on tables A and B, you specify the left outer
> > join when you want all of table B's rows, even when no corresponding
> > record exists in table A"
>
> > I had this down as correct but the answer in the book has it as wrong.
>
> > I'm pulling my hair out here!
>
> > Is it right or wrong please?
>
> As an informal description of outer joins generally then it's correct. I
> guess what the author meant was that A is written before B and that
> therefore the statement is supposed to describe a RIGHT outer join rather
> than LEFT. In other words table B is on the "preserved" side of the join
> ("all of table B's rows").
>
> I wouldn't worry too much about it. The distinction between left and right
> outer joins is an almost trivial matter of syntax.
> i.e. with all other things being equal:
>
> A LEFT OUTER JOIN B
>
> means the same as
>
> B RIGHT OUTER JOIN A
>
> If it helps, just imagine that all examples of "non-symetric" joins are LEFT
> outer joins.
>
> --
> David Portas

Nicely put. And why the inventors of the syntax decided the LEFT/RIGHT thing was necessary I'll never understand.

The way my brain works at least, if I write

FROM A, B or indeed

FROM A some form of join B

then I am conceptually "starting" with rows in A, and then going over to B to find rows that match what I've already got in A. (I say "conceptually" because of course the optimizer is free to turn this on its head if it sees a more efficient approach that is logically equivalent to what I've specified.)

The typical Outer Join scenario is that there might not be a matching row in B, but I want to keep my original set of A rows regardless (rather than excluding them as would otherwise happen). I express this in ANSI syntax as

FROM A LEFT OUTER JOIN B Why anyone would ever want to write that backwards using RIGHT OUTER JOIN I will never understand. The equivalent

FROM B RIGHT OUTER JOIN A means we should conceptually start in A and work to our left with B as the "weak" side of the join. That in my view is perverse. Outer joins are confusing enough already without writing them backwards. Perhaps RIGHT OUTER JOIN is more natural to people from right-to-left language backgrounds. Received on Sat Aug 18 2007 - 05:50:05 CDT