RE: ** SPAM scored: Med **RE: should one use ANSI join syntax when writing an Oracle application?

@Mindaugas:

"select count(*) from (select * from test1 t1 full outer join test2 t2 on t1.n = t2.n);"

This example is biased for you're are taking a small query where the logic can be grasped in one glimpse eye.  

I don't like the ANSI syntax because it mix the task performed in the FROM and in the WHERE clause.
In the old Oracle oracle all the join logic is in one block while in ANSI syntax is it is spread.
SQL stands for 'STRUCTURED query language' and by expanding the logic of the join and filter into the FROM clause, you destroy the localisation of the logic which used to reside solely in the WHERE clause.

Take the example of 20 tables each one with one additional filter condition.
In the Oracle logic, I will have smothing that looks like
.
.

And A.table.col1 = b.table.col1
And A.table.col2 = 'VALUE'
And b.table.col1 = c.table.col1 ....

With the Ansi syntax you can't write the join and filter aside and have a quick overview of the join method and filters.

Just a question of taste, for I think also a question of logic, which is 'keep the same type of role in the same geographical area'.

bp

-----Original Message-----
From: Mindaugas Navickas [mailto:mnavickas_at_yahoo.com] Sent: Wednesday, 18 October, 2006 5:53 AM To: breitliw_at_centrexcc.com; 'oracle-l'
Cc: 'Mladen Gogala'; niall.litchfield_at_gmail.com Subject: ** SPAM scored: Med **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





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