(+) oracle notation

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).

Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
Don't let people drive you crazy when you know it's in walking distance.

<bmccoy@chapelperilous.net> writes:

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.

Tomas

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.

On Thu, 19 Oct 2000 bmccoy@chapelperilous.net 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).

Oops, I meant 7.1!

Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
Love is staying up all night with a sick child, or a healthy adult.

Example of its use:
select
a.item_number,
b.group_code_description
from
items a,
group_codes b
where
a.group_code = b.group_code (+);
in this example all the records in table "items" appear in the output and
all those records in "group codes" that matches the group_code in items. The
"group_code_description" of all those non-matching records in "group_codes"
table are displayed as NULL.

----- Original Message -----
From: "Tomas Berndtsson" <tomas@nocrew.org>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, October 19, 2000 12:47 PM
Subject: Re: [GENERAL] (+) oracle notation

<bmccoy@chapelperilous.net> writes:

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

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).

In order to do that you should put the (+) operator at the "possible nulls"
side of the equality. It should go this way:
SELECT <fields> FROM <tables> WHERE table1.field = table2.field (+);

There is an artifical way to do the outer join in PostgreSQL... It
involves using UNION and NOT IN... see :

http://www.postgresql.org/docs/aw_pgsql_book/node305.html

Hope this helps...

-jag

--
"She who is wanting me,
Whose touch can make me cry,
I can only understand
By never asking her why..."
-John Popper

I'm not sure about the standard, but I really like Oracle's notation for
foreign keys:

select a.item_number, b.group_code_description
from items a, group_codes b
where a.group_code = b.group_code (+);

Much better than

select a.item_number, b.group_code_description
from items a outer join group_codes b on a.group_code = b.group_code;

In fact, it's MUCH BETTER when you have to join several tables (one thing
PgSQL is very good at, by the way). In such cases, the seconde syntax
requires an unreadable lot of ()'s in the from clause.

Don't mean to throw away the standard though, but having Oracle's (+) syntax
around too would be a big help.

"Edmar Wiggers" <edmar@brasmap.com> writes:

I'm not sure about the standard, but I really like Oracle's notation for
foreign keys:

select a.item_number, b.group_code_description
from items a, group_codes b
where a.group_code = b.group_code (+);

I beg to differ --- IMHO, Oracle's notation is brain dead. If they had
stuck the (+) markers on FROM-list items, it would have been sort of
reasonable, but as is, it's incomplete and ambiguous. Incomplete
because you can't specify an outer join against a table that's not
referenced anywhere in the WHERE clause. Ambiguous because it's not
clear what it means if you reference several columns from the same table
in WHERE, and tag some of them with (+) and others not. Does that mean
you get an outer join anyway? Is it an error condition? Maybe you
should implicitly get two FROM-list items, one outer joined and one not?

Worse, if you have more than two FROM-items it's very unclear what the
Oracle syntax means at all. There is a big difference between
(A CROSS JOIN B) LEFT JOIN C
and
A CROSS JOIN (B LEFT JOIN C)
not to mention
(A LEFT JOIN C) CROSS JOIN B
but who is to say which of these behaviors you will get from, say,
select ... from A, B, C where a1 = b1 and a2 = c2 (+)
? And if you reorder the terms in the WHERE, do you get a different
answer? It gets a lot worse if more than one table is outer-joined.

I don't have any great love for the ISO syntax either; it's certainly
mighty verbose. But at least you can tell what the heck it means.

regards, tom lane

Tom Lane wrote:

"Edmar Wiggers" <edmar@brasmap.com> writes:

I'm not sure about the standard, but I really like Oracle's notation for
foreign keys:

select a.item_number, b.group_code_description
from items a, group_codes b
where a.group_code = b.group_code (+);

I beg to differ --- IMHO, Oracle's notation is brain dead.

I agree. In my opinion the best syntax for outer joins was brought up
by informix. Ex:

SELECT a.f1, b.f1, c.f1
FROM a, OUTER( b, OUTER c )
WHERE a.key = b.fkkey
AND b.key = c.fkkey

Where b is outer from a and c outer from b. Precedence and
hierachical order is given by the parenthesis. Same example

FROM a, OUTER ( b, c)

b and c are outer from a. I find this syntax clear and logic without
any danger of missinterpretation

Best regards
Herbie

Sorry, maybe I confused you.

The Oracle way:
I failed to mention that (+) are specific to outer joins.
There is no way to express a join in the from clause.
Everything goes on the where clause: joins and "filter conditions".
In the where clause, it is common practice to express to specify first
your joins and after your filters.

Example:

select a.id,a.size,b.*,c.id,c.color
from table_a a, table_b b, table_c c
where
a.b_id = b.id and
a.c_id = c.id(+) and
a.size < 1000 and
b.weight > 10;

This is a select from 3 tables, where a and b are regularly joined, but c is
outer joined. That is, the query is likely to return null values on c.id and
c.color.

When you are joining 8 tables, that syntax becomes clearer.

I believe the standard syntax for that might be:

select a.id,a.size,b.*,c.id,c.color
from ((table_a a join table_b b on a.b_id = b.id) outer join table_c c on
a.c_id = b.id)
where
a.size < 1000 and
b.weight > 10;

To me, not so readable. But of course I can live with that.

Agreed.

Tom Lane wrote:

"Edmar Wiggers" <edmar@brasmap.com> writes:

I'm not sure about the standard, but I really like Oracle's notation for
foreign keys:

select a.item_number, b.group_code_description
from items a, group_codes b
where a.group_code = b.group_code (+);

I beg to differ --- IMHO, Oracle's notation is brain dead.

I agree. In my opinion the best syntax for outer joins was brought up
by informix. Ex:

SELECT a.f1, b.f1, c.f1
FROM a, OUTER( b, OUTER c )
WHERE a.key = b.fkkey
AND b.key = c.fkkey

Where b is outer from a and c outer from b. Precedence and
hierachical order is given by the parenthesis. Same example

FROM a, OUTER ( b, c)

b and c are outer from a. I find this syntax clear and logic without
any danger of missinterpretation

Best regards
Herbie

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

"Edmar Wiggers" <edmar@brasmap.com> writes:

select a.id,a.size,b.*,c.id,c.color
from table_a a, table_b b, table_c c
where
a.b_id = b.id and
a.c_id = c.id(+) and
a.size < 1000 and
b.weight > 10;

This is a select from 3 tables, where a and b are regularly joined, but c is
outer joined. That is, the query is likely to return null values on c.id and
c.color.

Yes, but outer joined *to what*? And what aspect of the syntax decides
that? The problem with this syntax is that it's not apparent when the
WHERE-clause conditions are applied. At least not to me.

The problem can be seen most easily when there are additional
restrictions on table C. Actually we don't need 3 tables, so consider

select * from table_a a, table_b b
where
a.id = b.id(+) and
b.weight > 10;

with data

a.id

1
2

b.id b.weight

1 20
2 5

Clearly we will get a row 1,1,20, and we will *not* get a row 2,2,5.
But will we get a row 2,NULL,NULL, or not? If the outer join occurs
after we remove b rows with weight<=10, then there will be no row
matching a.id=2, so the outer join will produce a row 2,NULL,NULL.
If it's done in the other order, the outer join will produce a row
2,2,5, which will then be discarded due to the condition weight>10,
so no row out. The ISO syntax allows both these behaviors to be
expressed unambiguously:

... FROM a LEFT JOIN b ON (a.id = b.id AND b.weight > 10)
... FROM a LEFT JOIN b ON (a.id = b.id) WHERE b.weight > 10

whereas the Oracle syntax is very ambiguous --- please don't tell me
that it depends on the ordering of the AND clauses in the WHERE!

(You may argue that 2,NULL,NULL violates the condition b.weight > 10,
but that's just an artifact of trying to simplify the example as much
as possible. If I write
where
a.id = b.id(+) and
(b.weight > 10 OR b IS NULL);
then it's absolutely unclear which result the Oracle syntax should
produce.)

It gets a lot worse if there are multiple tables being outer-joined,
since then it will depend on the join order whether you get certain
part-NULL rows out or not, and I see no way to define the join order
in the Oracle syntax.

I believe the standard syntax for that might be:

select a.id,a.size,b.*,c.id,c.color
from ((table_a a join table_b b on a.b_id = b.id) outer join table_c c on
a.c_id = b.id)
where
a.size < 1000 and
b.weight > 10;

To me, not so readable. But of course I can live with that.

Like I said, I don't much care for the ISO syntax either --- it's
very verbose. But it's unambiguous what will happen...

regards, tom lane

Got it. Thanks. Even though I'd never seen it like that, it is indeed
ambiguous.

In Oracle,

where
a.id = b.id(+) and
(b.weight > 10 OR b IS NULL);

is equivalent to

... FROM a LEFT JOIN b ON (a.id = b.id) WHERE b.weight > 10

That is, we get row 2,NULL,NULL.

To produce

... FROM a LEFT JOIN b ON (a.id = b.id AND b.weight > 10)

you have to use "in-line views" (Oracle term), like

... FROM a, (select * from table_b where b.weight > 10) b
WHERE a.id = b.id(+);

I guess that finishes it. Let's use STANDARD syntax and that's it. I guess
postgres would need a lot of massage on query parsing to get oracle's
mysterious syntax right.

Hi,

The Sybase notation for outer joins seems to be good too:

*=
or
=*

Claudio

Edmar Wiggers wrote:

Excuse me,

I should have written :

I t seems to be ambiguous !!! too ...

Claudio.

Claudio Jose Zanardi Grillo wrote:

Hi, all,

Having read through this discussion so far, the only thing that I would like
to add is that when using Oracle's (+) notation, one problem that I have run
into is the behaviour when outer joins are incorrectly specified. By this I
mean that not all of the outer join columns are correctly marked as such.
As an example (outer join format):

select *
from table1 outer join
table2
on table1.a = table2.z
and table1.b = table2.y

compared to (oracle format, and this has a mistake):

select *
from table1,
table2
where table1.a = table2.z
and table1.b = table2.y (+)

The (+) for the first column got forgotten. Now, what should I get in my
resultset?

To be honest, I haven't spent too much time working out, or finding out, how
Oracle deals with this. However, I think that in terms of joins, it's
nonsensical. You either inner join, or outer join, not half-join. If we're
going to implement this operator (+), then I would propose that an error was
raised here, or at least a NOTICE.

And don't forget that you can (+) a between as well:

select *
from table1,
table2
where table1.a
between table2.z (+)
and table2.y (+)

Unless, of course, someone can show any reason why this would make sense.

Cheers...

MikeA