Skip site navigation (1) Skip section navigation (2)

Re: Complex outer joins?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
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 22:33:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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)


     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

In response to


pgsql-sql by date

Next:From: G. Anthony ReinaDate: 2003-03-26 22:47:11
Subject: Re: How can I pass an array from PostgreSQL to C (libpq)?
Previous:From: Andreas PflugDate: 2003-03-26 19:08:41
Subject: Re: UPDATE FROM portability

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group