X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id A64C1475CBC
	for <pgsql-sql@postgresql.org>; Thu, 27 Mar 2003 00:36:08 -0500 (EST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 9D38D47592C
	for <pgsql-sql@postgresql.org>; Thu, 27 Mar 2003 00:36:04 -0500 (EST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id ECD1FD5FA; Wed, 26 Mar 2003 21:36:09 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id E29735C0B; Wed, 26 Mar 2003 21:36:09 -0800 (PST)
Date: Wed, 26 Mar 2003 21:36:09 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	"Correia, Carla" <Carla.Correia@logicacmg.com>,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: Complex outer joins?
In-Reply-To: <878yv1l981.fsf@stark.dyndns.tv>
Message-ID: <20030326212439.J70893-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Spam-Status: No, hits=-26.0 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,
	REPLY_WITH_QUOTES autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200303/606
X-Sequence-Number: 12644

On 26 Mar 2003, Greg Stark wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>
> > On Wed, 26 Mar 2003, Tom Lane wrote:
> >
> > >     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.
>
> Yeah, that's not a particularly enlightening case because if you convert the
> right joins to left joins you see that these aren't actually similar queries
> at all.
>
> the first is
>  "C left join (G left join L)"
> and the second is
>  "G left join (C left join L)"
>
> They only look similar superficially when written using right joins but
> they're actually totally different structures.

Right, but it would have been an issue in converting to sql form if you
had a set of conditions like C.a=L.a(+) and G.a=L.a(+) except that from
testing that appears to be invalid, so you can't get into the question of
which plan is correct.

> The other example using IS NULL on a column produced by the outer join is more
> fundamental. I'll have to ponder that one. I don't remember how Oracle behaved
> with cases like that because I rarely used that idiom. I think I rarely used
> it because I found it too confusing with Oracle's (*) syntax which I suppose
> begs the question.

You probably can't easily do IS NULL with the oracle syntax, but coalesce,
case or non-strict user defined functions appear to have a similar effect
(and at least in the coalesce case not rejected and give one of the two
expected outputs).