Re: Outer join by (+)

From: DA Morgan <>
Date: Wed, 02 Jan 2008 08:47:50 -0800
Message-ID: <>

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

>> On Sat, 29 Dec 2007 10:56:45 +0000, Adam Cameron
>> <> 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.

> You should add a "for me" at the end of the sentence above. This is
> exactly what Adam's comment was all about: some prefer one and others
> prefer the other.
>> 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.

> What exactly is the "SQL paradigm"? I know only a SQL standard (or
> rather several versions of it) which defines certain semantics for
> particular syntactical constructs. As far as I can see JOIN's are
> properly defined within the standard and do not break anything (because
> it was a new construct when added).
>>> 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.

> This still leaves the issue of lesser expressiveness for the proprietary
> Oracle syntax (see the link I posted earlier).
> Kind regards
> robert

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

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

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 (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Wed Jan 02 2008 - 10:47:50 CST

Original text of this message