Is there an Oracle official recommendation on the use of explicit ANSI JOINs vs implicit joins?

Question

Note: I am not asking you to tell me “use explicit joins” but looking for Oracle official position if any on that subject.

From Oracle database documentation (also appears in 9i and 11g documentations):

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 […]

In other words, Oracle advises to prefer the first of these two forms:

FROM a LEFT JOIN b ON b.x = a.x
vs
FROM a, b WHERE b.x(+) = a.x

However, I have never found in any Oracle documentation a single recommendation to use preferably one of those two forms:

FROM a INNER JOIN b ON b.x = a.x
vs
FROM a, b WHERE b.x = a.x

Is there a paragraph I missed?

Answer 1

There are a number of notes From Oracle Support site on issues with ANSI join syntax with workarounds recommeding to use the oracle syntax.

Bug 5188321 wrong results (no rows) OR ORA-1445 from ANSI outer join

Versions affected: Versions >= 9.2.0.1 but < 11 

Description
Wrong results or an ORA-1445 can be returned with a query involving a 
 very large select list count when ANSI OUTER JOIN syntax is used.


Workaround
  Use native oracle outer join syntax 
 or 
  reduce the select list count.

Bug 5368296 ANSI join SQL may not report ORA-918 for ambiguous column

Versions affected: Versions < 11

Description

****
Note: This fix introduces the problem described in bug 7318276
      One off fixes for that bug address the issue here also.
****      

ORA-918 is not reported for an ambiguous column in a query 
involving an ANSI join of more than 2 tables/objects. 

eg:
 -- 2 table join, returns ORA-918
 SELECT  empno 
 FROM emp a JOIN emp b  on a.empno = b.empno; 

 -- 3 table join does not report ORA-918 when it should ...
 SELECT  empno
 FROM emp a JOIN emp b on a.empno = b.empno
            JOIN emp c on a.empno = c.empno;

Bug 7670135 Long parse time compiling ANSI join

 Versions affected: Versions BELOW 11.2 

Description

A query having ANSI join(s) may take noticeable time during query compilation,
especially if the query includes an NVL() function.

Workaround:
 Use ORACLE join instead of ANSI join

From the Oracle Press - Oracle OCP 11g all in one exam guide

And from asktom (who is non committal)

 Historically there have been bugs related to ANSI syntax, in fact even the 
 10.2.0.4 projected issues list includes 10 bugs/issues related to ANSI syntax.

 In the past I've encountered some of these bugs myself, and have continued to use 
 and advocate the "traditional" Oracle style.

 I'd like to know if you feel that the implementation of ANSI syntax is now equally    
 robust compared  to the traditional syntax.

 Followup   February 19, 2008 - 5pm Central time zone:
 unfortunately, there are bugs in non-ansi joins too, probably more than 10 in fact.

 I personally do not use the new syntax (except in the rare case of a full outer join, 
 a truly rare beast to encounter). I have no comment on it really. 

See also earlier question on same topic Difference between Oracle's plus (+) notation and ansi JOIN notation?

I also found this statement in a document but no reference as to where it came from

"Starting with Oracle 9i, Oracle recommends that SQL developers use the ANSI join syntax instead of the Oracle proprietary (+) syntax. There are several reasons for this recommendation, including:

• Easier to segregate and read (without mixing up join versus restriction code) • Easier to construct join code correctly (especially in the case of “outer” joins) • Portable syntax will work on all other ANSI compliant databases, such as MS SQL Server, DB2, MySQL, PostgreSQL, et al • Since it’s the universally accepted standard, it’s the general target for all future database and third party vendors’ tools • The proprietary Oracle outer-join (+) syntax can only be used in one direction at a time, it cannot perform a full outer join • Plus these additional limitations from the Oracle documentation: o 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. o A condition containing the (+) operator cannot be combined with another condition using the OR logical operator. o A condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression. o A condition cannot compare any column marked with the (+) operator with a sub-query."

Thus it’s time to embrace the ANSI join syntax – and move into the 21st century

Answer 2

I haven't seen it if there is. The reason for preferring ANSI syntax for outer joins in particular (apart from the non-standarrd, Oracle-specific (+) symbol) is that more outer joins are expressible using the ANSI syntax. The restriction "ORA-01417: a table may be outer joined to at most one other table" applies to (+) outer joins but not to ANSI outer joins. Other restrictions on (+) that do not apply to ANSI outer joins are documented here.

One highly respected Oracle expert actually recommends sticking to the old syntax for inner joins - see Jonathan Lewis's blog. He says there that ANSI joins are transformed to traditional Oracle joins under the covers anyway. I don't agree with him 100% (I prefer ANSI joins myself in general), but would not claim to have a fraction of his knowledge on the topic.

In a nutshell, ANSI outer joins are technically superior to old (+) joins, whereas with inner joins it is more just a matter of style.