Re: Outer join by (+)

Robert Klemme wrote:
> On 02.01.2008 15:33, Brian Tkatch wrote:

>> On Sat, 29 Dec 2007 10:56:45 +0000, Adam Cameron
>> <adam_junk_at_hotmail.com> wrote:
>>
>>>> But when I use (+) syntax, it will be easy to design and understand.
>>> Not if the person reviewing your code is more accustomed to 
>>> ANSI-standard SQL than out-of-date Oracle peculiarities.
>>>
>>> I can read the standard SQL just fine, because that's what I'm used 
>>> to.  I have to stop and think about the Oracle-specific stuff because 
>>> I'm less familiar with it.
>>>
>>> I presume it's the other way around for you for the reverse reasons.
>>>
>>
>> I find the (+) is very intuitive. In describes the join in the WHERE
>> clause quite well. Basically, it means that that this join is a join
>> "plus". It just takes a moment to realize that is what it means.
>>
>> The ANSI syntax, however, is downright confusing.

>> Further, i think it breaks the SQL paradigm. A FROM clause includes, a
>> WHERE clause excludes. Another TABLE in the FROM clause includes it,
>> saying it is not Cartesian, is an exclusion. Therefore, it belongs in
>> the WHERE clause.

>>> Both situations hereare entirely reasonable, I think, but *looking 
>>> forward*, sticking to standards-conforming SQL is probably the better 
>>> approach.
>>>
>>> IMO.
>>
>> Good point. I would like to counter point.
>>
>> A person looking forward to a maintenance coder who probably will
>> spend less time than required modifying a query, may have a harder
>> time figuring out where to put a new clause. That is, whether it goes
>> in the FROM clause or the WHERE clause, and how it affects to total
>> join, as nesting is a big issue. However, when all clauses are kept in
>> the WHERE clause, the maintenance coder knows exactly where it goes,
>> and, IMO, is more likely to do it correctly.

The endless, and meaningless, debate about which is more intuitive or which has the prettiest color, or whatever is amusing. Lets look at reality.

SELECT *
FROM t1, t2
WHERE t1.col = t2.col;

replace the comma with the word JOIN
replace the word WHERE with the word ON
and you get

SELECT *
FROM t1 JOIN t2
ON t1.col = t2.col;

Lets see one byte becomes four so add 3
and five bytes become two so subtract 3

And for this people get excited?

No doubt with a RIGHT OUTER JOIN you can stave off carpal tunnel syndrome for an additional 15 minutes by using (+) but is this really worth all the keystrokes that go into this seemingly endless discussion?

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org