RE: should one use ANSI join syntax when writing an Oracle application?
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Wed, 18 Oct 2006 13:15:35 -0700
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@xxxxxxxxxxxxx]
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@xxxxxxxxx>:
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
