RE: prefer (+) oracle notation

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

>> -----Original Message-----
>> From: Edmar Wiggers [mailto:edmar(at)brasmap(dot)com]
>> Sent: 19 October 2000 21:18
>> To: pgsql-general
>> Subject: [GENERAL] prefer (+) oracle notation
>>
>>
>> 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.
>>