Re: Complex outer joins?

On Wed, 26 Mar 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > On 26 Mar 2003, Greg Stark wrote:
> >> Can you show an example where the join order would affect the result set? I
> >> can't think of any.
>
> > I can think of a few somewhat degenerate cases.
>
> I don't think you need a degenerate case. Consider
>
> from (G left join L on (G.SELID = L.SELID))
> right join C on (L.SELID = C.SELID)
>
> versus
>
> from G left join
> (L right join C on (L.SELID = C.SELID))
> on (G.SELID = L.SELID)

I'd noticed that too, but I was trying to do it without changing the
type of join since I wasn't sure whether he'd meant one using only left
joins or outer joins in general.

> The difference between this and the query we were discussing is just use
> of "right" rather than "left" in the second JOIN. AIUI, in the Oracle
> syntax this difference would be expressed by moving the (*) from one
> side to the other of the L.SELID = C.SELID clause. Unless they have
> strange restrictions on the combinations of clauses you can mark with
> (*), I don't see how they can assume that join order is insignificant.

I'd guess that they might check that the conditions are in a specific set
of limited conditions in order to allow the reordering and disallow the
reordering otherwise (well, I'd hope that they do this if they ever
allow reordering).