From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "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-27 05:36:09 |
Message-ID: | 20030326212439.J70893-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 26 Mar 2003, Greg Stark wrote:
> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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).
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-03-27 06:18:19 | Re: Complex outer joins? |
Previous Message | Rudi Starcevic | 2003-03-27 04:57:17 | Re: Off topic : world database |