RE: ** SPAM scored: Med **RE: should one use ANSI join syntax when writing an Oracle application?
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Bernard.Polarski@xxxxxxxxxxxxxx
- Date: Wed, 18 Oct 2006 05:42:11 -0600
At 02:02 AM 10/18/2006, Polarski, Bernard wrote:
@Mindaugas:
"select count(*) from (select * from test1 t1 full outer join test2 t2
on t1.n = t2.n);"
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.
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".
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 ....
Also makes it much easier to spot opportunities to add - or change -
join predicates based on transitive closure which can make a big
difference for the access path and performance. Been there, done that.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
