RE: should one use ANSI join syntax when writing an Oracle application?
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Oct 2006 11:54:37 -0700
I'm not up on standard documents (I haven't EVER read an ANSI SQL standard
document), so I didn't know that the traditional Oracle style of joins
select * from a,b where a.id = b.id
was also an ANSI standard. This site(
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ), found at
Wikipedia, seems to have the SQL 1992 standards, but either the document is
not complete or else it's not explicit on the subject.
I didn't see anywhere in the concepts manual or the SQL reference manual that
the join operators introduced in Oracle 9.x were SQL92 syntax. The SQL
Reference (
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_standard_sql.htm#10293
) mentions SQL:1999 standards.
To be honest, I'm not interested enough in the subject to actually order the
standards and read them ( http://www.techstreet.com/features/ISO_IEC_9075.tmpl
) and anyway following standards isn't really my main concern.
Right now I am less decided after reading the second day's discussion, than I
was yesterday! I suppose I will stick to the traditional syntax and use the SQL
92/99 syntax only when necessary, since that's what I am more used to, and
there doesn't seem to be a really strong reason (or consensus) to convert to
new syntax.
Though personally, I still think that
select *
from a
inner join b using (id)
inner join c on (b.id = c.id and b.order_type = c.order_type_code)
where a.state_code = 'FL' and c.order_date >= sysdate - 7 ;
is logically more satisfying and easier to parse visually (what are the columns
joining b to c?) than the traditional method
select *
from a, b, c
where a.id = b.id
and b.id = c.id and b.order_type = c.order_type_code
and a.state_code = 'FL' and c.order_date >= sysdate - 7 ;
because in the traditional method you can move the conditions around in the
where clause, like this
select *
from a, b, c
where a.id = b.id and a.state_code = 'FL'
and b.id = c.id and c.order_date >= sysdate - 7
and b.order_type = c.order_type_code ;
-----Message d'origine-----
De la part de Wolfgang Breitling
Just to add another bit of nitpicking. What we call "old', Oracle or
"traditional" SQL
is also ANSI SQL (without the outer join extension which isn't
mentioned in the subject line but everyone seems to use in the
examples) just an older ANSI standard. I suppose the OP means ANSI
SQL92 join syntax. Again, I haven't read the SQL92 standard but I
presume that the traditional way of coding a join is still in there
as well making both ways "ANSY join syntax".
--
//www.freelists.org/webpage/oracle-l
Other related posts:
