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?

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

From: Allen, Brandon <>
Date: Mon, 16 Oct 2006 13:55:35 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E217D@NT15.oneneck.corp>

Here is what Oracle has to say about it - from 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:

        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.

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

        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.

Received on Mon Oct 16 2006 - 15:55:35 CDT

Original text of this message