Re: (+) oracle notation

On 19 Oct 2000, Tomas Berndtsson wrote:

> > On Thu, 19 Oct 2000, Mathieu Arnold wrote:
> >
> > > I just discovered the (+) oracle join notation, is there something like
> > > this in postgres ?
> >
> > That's the outer join operator for Oracle, right?  I believe that outer
> > joins are coming in 7.2, but I don't know if that means the operator will
> > be (I don't think it's standard).
>
> What does the operator do? I know what an outer join does, but not
> where that operator comes into the picture.

It's used, in Oracle, like this:

SELECT <fields> FROM <tables> WHERE table1.field (+) = table2.field;

This will return all appropriate rows in table1, even if no
corresponding value exists in table2 (it'll list an empty value for that
column in the output tuples if no value exists). Oracle doesn't seem to
use the OUTER JOIN keywords other databases use.  You can also put the (+)
after the second field to get all of the rows in the second table even if
no value exists in the first table.

I'm looking forward to having outer joins in PostgreSQL, (+) operator or
not.  It is the one feature, after foreign keys, that I had been missing
in PostgreSQL for a couple of years now and am quite happy they are making
into the next release.

Brett W. McCoy
                                              http://www.chapelperilous.net
---------------------------------------------------------------------------
I'll be Grateful when they're Dead.