Re: Outer join by (+)

From: Robert Klemme <>
Date: Wed, 02 Jan 2008 17:28:05 +0100
Message-ID: <>

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 Received on Wed Jan 02 2008 - 10:28:05 CST

Original text of this message