Re: should one use ANSI join syntax when writing an Oracle application?

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 17 Oct 2006 20:55:09 -0600

I am on Mladen's side (at least as far as this topic is concerned) and I prefer the "old", or rather "traditional" (sounds better, doesn't it) style. I don't share Niall's opinion that a join predicate is different from an "ordinary" predicate.
I must confess my complete ignorance re ANSI syntax but regarding your preference for the ANSI syntax because it separates the two types of predicates. How would you express the following with ANSI style join syntax:

select ... from A, B where A.x < B.y


select ... from A, B where A.x between B.y and B.z

or any of the other non-equi joins.

Provided it is possible I have the feeling that will be hardly as concise and clear as the traditional syntax.

As far as the subtopic of outer joins is concerned, I don't like them in either syntax. Technically they are not necessary and are just a shortcut for a union (all) of two sql - or three for a full outer join (shudder). Maybe if the shortcut didn't exist there would be fewer of them.

At 07:18 AM 10/17/2006, Niall Litchfield wrote:
On 10/17/06, Mladen Gogala <<mailto:mgogala@xxxxxxxxxxx>mgogala@xxxxxxxxxxx> wrote:

On 10/17/2006 01:43:14 AM, Niall Litchfield wrote:
> My personal preference is for the ANSI standard syntax, I really find that
> seperating out the joins from the other conditions helps me understand more
> easily what a query is trying to achieve. It also helps debugging IMO.

How, exactly are joins different, in your opinion?

it seems to me that where emp.deptno = dept.deptno is an entirely different fish from where emp.deptno=10, in the former case you are saying how employees and departments are related, in the latter you are specifying what criteria you are using to choose data.


Wolfgang Breitling
Centrex Consulting Corporation

Other related posts: