RE: should one use ANSI join syntax when writing an Oracle application?
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <Jacques.Kilchoer@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Oct 2006 13:55:35 -0700
Here is what Oracle has to say about it - from
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/quer
ies006.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
Sent: Monday, October 16, 2006 1:29 PM
To: oracle-l
Subject: should one use ANSI join syntax when writing an Oracle
application?
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?
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
Other related posts:
