From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Stark <gsstark@mit.edu>
Cc: Correia, Carla <Carla.Correia@logicacmg.com>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Complex outer joins?
Date: Wed, 26 Mar 2003 15:02:04 -0800 (PST)
Message-ID: <20030326145855.J66362-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <7039.1048718031@sss.pgh.pa.us>
On Wed, 26 Mar 2003, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone23.bigpanda.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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, tgl@sss.pgh.pa.us, gsstark@mit.edu, Carla.Correia@logicacmg.com
Subject: Re: Complex outer joins?
In-Reply-To: <20030326145855.J66362-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox