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

My personal preference is for the ANSI standard syntax, I really find that seperating out the joins from the other conditions helps me understand more easily what a query is trying to achieve. It also helps debugging IMO.

As for bugs, well there was a famous bug in 9.0.1 (base release) that rendered the database insecure, but I can't say that since then I have come across any great instances of wrong results with them - and I'd wager they were fewer than wrong results due to my not writing a query accurately first time. You could certainly look at the known issues lists (I referred to them at
http://orawin.info/services/index.php?option=com_content&task=view&id=65&Itemid=34) and the fixed bugs lists in 10.2.0.2 and 9.2.0.7.

So I'd say that by and large my experience has been positive and that I find the style a lot easier to understand.

On 10/16/06, Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> wrote:
>
> 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:
>
> -
>
> 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 TABLEclause) 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_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?
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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