Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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?
De : Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com]
Here is what Oracle has to say about it - from http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm:
Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:
However, the following self join is valid:
SELECT e1.employee_id, e1.manager_id, e2.employee_id FROM employees e1, employees e2 WHERE e1.manager_id(+) = e2.employee_id;
* The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
* 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.
________________________________ From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jacques Kilchoer Let us suppose that I were to be writing a PL/SQL package that will be installed on a variety of databases in my company, and not all the databases are at the same version or patch level (but all are 9.2 and up). Would it be a good idea to use ANSI join syntax in the queries in the package? Knowing that a non-negligeable portion of the queries will be using outer joins? When I search on Metalink, I see bugs relating to the ANSI join syntax, but I also see bugs reported against the (+) style outer join syntax. Of the bugs I see mentioned on Metalink, many were "unpublished" bugs so I couldn't see the details. I was thinking of using the ANSI join syntax because it is supposed to be easier to read and understand for people coming from non-Oracle environments, more portable, perhaps even "the wave of the future" (?) as I heard it described in one presentation. Would it be a mistake to use the ANSI join syntax? Should I stick with the old-fashioned Oracle syntax for another year or so?
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 16 2006 - 16:20:21 CDT