Re: Complex outer joins?

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)

In the former case you will see rows out for every SELID existing in C;
in the latter case, rows out for every SELID existing in G, which may
include rows having no match in C. The set of joined rows is the same
in either case, but the set of rows added for unjoined keys differs.

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.

regards, tom lane