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

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

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