RE: should one use ANSI join syntax when writing an Oracle application?
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Oct 2006 14:20:21 -0700
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@xxxxxxxxxxx]
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:
* You cannot specify the (+) operator in a query block that also contains
FROM clause join syntax.
* The (+) operator can appear only in the WHERE clause or, in the context
of left-correlation (that is, when specifying the TABLE clause) in the FROM
clause, and can be applied only to a column of a table or view.
* If A and B are joined by multiple join conditions, then you must use
the (+) operator in all of these conditions. If you do not, then Oracle
Database will return only the rows resulting from a simple join, but without a
warning or error to advise you that you do not have the results of an outer
join.
* The (+) operator does not produce an outer join if you specify one
table in the outer query and the other table in an inner query.
* You cannot use the (+) operator to outer-join a table to itself,
although self joins are valid. For example, the following statement is not
valid:
-- The following statement is not valid:
SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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?
Other related posts:
