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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 18 Oct 2006 13:15:35 -0700
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9ADF@ALVMBXW05.prod.quest.corp>

Considering your reputation on SQL tuning issues, I am not going to ask WHY you would want to do such a thing :)

Maybe this is not the right way to look at it, but when I write statements with the new syntax, I interpret it this way:

a inner join b using (id)
inner join c ...
 /* at this point I am joining c to the "row" created by joining a and b , so I can use columns from a AND b */

Your example would be

select A.x, B.y, C.z
from A
 inner join B on ( A.x = B.x )
 inner join C on ( A.x = C.x and B.x = C.x ) ;

Of course, without looking at the execution plan of the devious example you have no doubt set up, I can't tell you if this is really equivalent.

Example:

SQL> select t1.value, t2.value, t3.value   2 from t1
  3 inner join t2 on (t1.id = t2.id)
  4 inner join t3 on (t1.id = t3.id and t2.id = t3.id) ;

VALUE VALUE VALUE
---------- ---------- ----------

one        un         eins
six        six        sechs
seven      sept       sieben

(using these tables)

drop table t1 ;
drop table t2 ;
drop table t3 ;
create table t1 (id number, value varchar2 (10)) ;

create table t2 (id number, value varchar2 (10)) ; create table t3 (id number, value varchar2 (10)) ; insert into t1 (id, value)
 select 1, 'one' from dual

 union all select 2, 'two' from dual
 union all select 3, 'three' from dual
 union all select 4, 'four' from dual
 union all select 5, 'five' from dual
 union all select 6, 'six' from dual
 union all select 7, 'seven' from dual ;

insert into t2 (id, value)
 select 1, 'un' from dual

 union all select 3, 'trois' from dual
 union all select 5, 'cinq' from dual
 union all select 6, 'six' from dual
 union all select 7, 'sept' from dual
 union all select 8, 'huit' from dual
 union all select 9, 'neuf' from dual

 union all select 10, 'dix' from dual ;

insert into t3 (id, value)
 select 1, 'eins' from dual

 union all select 2, 'zwei' from dual
 union all select 4, 'vier' from dual
 union all select 6, 'sechs' from dual
 union all select 7, 'sieben' from dual
 union all select 8, 'acht' from dual
 union all select 10, 'zehn' from dual

 union all select 11, 'elf' from dual
 union all select 12, 'zwoelf' from dual ;

commit ;

-----Message d'origine-----
De : Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Envoyé : mercredi, 18. octobre 2006 12:55 À : Jacques Kilchoer
Cc : oracle-l
Objet : RE: should one use ANSI join syntax when writing an Oracle application?

Quoting Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>: I need some help here. How do I code a full transitive closure join with the new syntax? In the traditional syntax I can say:

select A.x, B.y, C.z
from A, B, C

where A.x = B.x
  and B.x = C.x
  and A.x = C.x

/

How do I do that with the newfangled syntax. When I try

select A.x, B.y, C.z
from A

 inner join B on ( A.x = B.x )
 inner join C on ( B.x = C.x )
 inner join C on ( A.x = C.x )

;

I get
select A.x, B.y, C.z
*
ERROR at line 1:
ORA-00918: column ambiguously defined

> And to answer this question: converting from the traditional syntax to newer
> ANSI syntax is easy, use the inner join and on keywords.
>
> select ... from A, B where A.x < B.y
> becomes
> select ... from A inner join B on ( A.x < B.y )
>
> select ... from A, B where A.x between B.y and B.z
> becomes
> select ... from A inner join B on ( A.x between B.y and B.z )
>

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

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

Received on Wed Oct 18 2006 - 15:15:35 CDT