Re: Going mad - outer join problem

From: Tim X <>
Date: Tue, 21 Oct 2008 18:27:02 +1100
Message-ID: <>

Jeremy <> writes:

> In article <>, says...>
>> select *
>> from a,
>> b
>> where = (+)
>> and nvl(b.code(+),'TYPE') = 'TYPE'
>> order by;
> 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 :-)


tcross (at) rapttech dot com dot au
Received on Tue Oct 21 2008 - 02:27:02 CDT

Original text of this message