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

Excellent! I too have written fat queries. I prefer writing phat ones these days. ;)  

One reason I've used ANSI syntax is where table B is outer joined to A and table C is outer joined to B. In a single statement, that scenario is illegal with Oracle's (+) notation. I tend to like the more verbose ANSI in very complex statements, but in one case I've reverted back to (+) because I couldn't get the query plan to be as effecient (9.2.0.5 DB), and didn't have the time to figure it out.  

Rich

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jacques Kilchoer Sent: Monday, October 16, 2006 4:20 PM
To: oracle-l
Subject: RE: should one use ANSI join syntax when writing an Oracle application?

I also found that when reading about ANSI joins at asktom.oracle.com, but I notice that Oracle seems to be recommending the ANSI outer join syntax since it allows you to write queries that couldn't be written with the (+) operator. However, all the queries I have written so fat that use outer joins can be written with the (+) operator.  

The main thing I gather from the description below is that, perhaps, Oracle intends to add enhancements to the ANSI join syntax that it won't add to the old-style join syntax? (e.g. the last 3 items in your documentation excerpt: A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator; A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression; A WHERE condition cannot compare any column marked with the (+) operator with a subquery.)  

My question, I guess, is have many people run into bugs with ANSI joins, or are the bugs rare and only happening with very convoluted queries?          

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