To: "Correia, Carla" <Carla.Correia@logicacmg.com>
Cc: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: Complex outer joins? 
In-reply-to: 
 <0F232CC93A58D6119C1600B0D0799B8102338822@hamsrvmx03.logica.co.uk>
References: <0F232CC93A58D6119C1600B0D0799B8102338822@hamsrvmx03.logica.co.uk>
Comments: In-reply-to "Correia, Carla" <Carla.Correia@logicacmg.com>
	message dated "Mon, 24 Mar 2003 10:49:53 +0000"
Date: Mon, 24 Mar 2003 10:18:10 -0500
Message-ID: <12785.1048519090@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Correia, Carla" <Carla.Correia@logicacmg.com> writes:
> Simplified example:
 
>  select G.SELID, G.TEXT, 
>  	L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT,
>  	C.ID as KRITERIENFELDID, C.SELFLD
>   from  G,  L,  C 
>   where 
>  	and G.SELID = L.SELID (+) 
>  	and L.SELID = C.SELID (+) 
>  	and L.SELLEVEL = C.SELLEVEL (+) 
 
> How can i write this in Postgres?

One of the un-fun things about Oracle's nonstandard syntax is that you
can't easily tell what the join order is supposed to be.  (At least I
can't; anyone know how this will get interpreted?)

The SQL-standard way of writing this would presumably be either

    from G left join L on (G.SELID = L.SELID)
         left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL)

or

    from G left join
         (L left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL))
         on (G.SELID = L.SELID)

depending on which join you think ought to be done first.  It might be
that the results are the same in this case, but I'm not convinced of
that.  In general the results of outer joins definitely depend on join
order.

			regards, tom lane