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

Hi,

I am afraid - I am not on "popular site of the table". Here I did a small test (Oracle 10.2.0.1.0):

create table test1 as
with m as ( select /*+ materialize */ 1 from all_objects where rownum <= 1000)
select TRUNC(dbms_random.value(400000,1000000)) n,dbms_random.string('A',10) v
from m,m;
create table test2 as
with m as ( select /*+ materialize */ 1 from all_objects where rownum <= 1000)
select TRUNC(dbms_random.value(0,600000)) n,dbms_random.string('A',10) v from m,m;
create index i_test1 on test1(n);
create index i_test2 on test2(n);
begin
dbms_stats.gather_table_stats('MNTEST','TEST1',method_opt=>'FOR ALL COLUMNS SIZE AUTO');
dbms_stats.gather_table_stats('MNTEST','TEST2',method_opt=>'FOR ALL COLUMNS SIZE AUTO');
end;
/
select count(*) from
(select * from test1 t1 full outer join test2 t2 on t1.n = t2.n); -- COST by running autotrace = 6953

select count(*) from
(select * from test1 t1, test2 t2
where t1.n (+) = t2.n
union
select * from test1 t1, test2 t2
where t1.n = t2.n (+));
-- COST by running autotrace = 42375

Which syntax is more readable is matter of taste - isn't it?

Regards
Mindaugas Navickas

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling
Sent: October 17, 2006 10:55 PM
To: oracle-l
Cc: Mladen Gogala; niall.litchfield_at_gmail.com Subject: Re: should one use ANSI join syntax when writing an Oracle application?

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

or

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 <mgogala_at_verizon.net> 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.  

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l