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@brasmap.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.
>>
