From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Greg Stark <gsstark(at)mit(dot)edu>, "Correia, Carla" <Carla(dot)Correia(at)logicacmg(dot)com>, "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Complex outer joins? |
Date: | 2003-03-26 23:02:04 |
Message-ID: | 20030326145855.J66362-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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).
From | Date | Subject | |
---|---|---|---|
Next Message | fabio | 2003-03-26 23:04:48 | Re: Executing SQL commands via triggers without the use of procedures |
Previous Message | Ryan | 2003-03-26 22:56:39 | Re: Off topic : world database |