RE: should one use ANSI join syntax when writing an Oracle application?
- From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Oct 2006 08:32:09 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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?
Other related posts:
