Re: Going mad - outer join problem

Jeremy <jeremy0505_at_gmail.com> writes:

> In article <48FB79A2.7010505_at_gmail.com>, mdemenko_at_gmail.com says...>
>> select *
>> from a,
>> b
>> where a.id = b.id (+)
>> and nvl(b.code(+),'TYPE') = 'TYPE'
>> order by a.id;
>>
>
> is the right answer. Thanks and also to David and Serge for their help.
>
> When I was taught SQL by oracle I don't recall there ever being
> references to e.g. "LEFT OUTER JOIN" - it was all the (+) notation.
>
> I guess they are methods to achieve the same objective but what are the
> pros and cons?

Funny enough, I saw some Oracle documentation today that recommends the OUTER JOIN ON from clause over the (+).

From my brief reading of the text, it seems it has the following advantages

• Don't have to remember to put (+) on all the statements in the where clause asociated with the join
• Does not have the restrictions on 'IN', 'OR' clauses that (+_ does
• Has additional functionality re: partitioned data and being able to join over patitions

however, I think my favorite advantage is that its right there 'in your face' so to speak. The (+) is easy to miss when maintaining someone elses code, plus more people are likely to be familiar with the 'OUTER JOIN ON' syntax than (+) and of course, as yo don't have to remember to put the (+) on all clauses in your where clause that involve the outer table, its likely to be less prone to hair ripping bugs (but you probably knew that one already :-)

Tim

-- 
tcross (at) rapttech dot com dot au