Re: Outer join by (+)

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. It is not easy to see which join happens where, and the difference between inner and outer joins seems less clear (because it is the type of join rather than the type of column clause).

Even though i know what the ANSI sytax is, i always have to rack by brains to translate to myself which join happens where, when there are three or more joins. The (+) is overwhelmingly clear, because there is no nesting of the clauses required. Though, it can be argued that SQL Server's *= is even clearer.

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.

B. Received on Wed Jan 02 2008 - 08:33:05 CST