From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
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 17:33:51 -0500
Message-ID: <7039.1048718031@sss.pgh.pa.us> (raw)
In-Reply-To: <20030326074807.H61005-100000@megazone23.bigpanda.com>
References: <20030326074807.H61005-100000@megazone23.bigpanda.com>
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)
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
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: tgl@sss.pgh.pa.us, sszabo@megazone23.bigpanda.com, gsstark@mit.edu, Carla.Correia@logicacmg.com
Subject: Re: Complex outer joins?
In-Reply-To: <7039.1048718031@sss.pgh.pa.us>
* 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