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

Thank you all for your opinions and comments.  

I, like Mr. Litchfield, like the feature that with the ANSI join syntax the where clauses are separated from the join clauses (something that can be accomplished with the Oracle syntax, but is more strictly enforced with the ANSI join syntax.)  

I agree that natural joins are not something that should be put in source code, only used in an interactive query to avoid typing: there is too much of a risk, with a natural join, that the addition of a column to a table can cause the query to return erroneous results. The using clause does not have the same problem, but one of the disadvantages of the using clause is that it forces you to remove the table qualifier before a column name, e.g.

SQL> select a.deptno
  2* from scott.emp a inner join scott.dept b using (deptno) select a.deptno

       *
ERREUR à la ligne 1 :
ORA-25154: column part of USING clause cannot have qualifier

I like to qualify every column in my multi-table SQL statements, even if it's unnecessary.

Using the ON clause (tableA inner join tableB on (tableA.column1 = tableB.column2)) is also more comfortable since it's the closest to the Oracle syntax (where tableA.column1 = tableB.column2)  

Another thought I had, in favour of the ANSI join syntax, is that this might be the future direction in which Oracle is pushing us: for example, for new features of outer joins, one MUST use the ANSI join syntax, as was mentioned by Brandon Allen. Could this mean that some new features will require the use of ANSI joins, and that the old-style joins will become the RBO of the select syntax? Or am I being paranoid?

De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de Niall Litchfield Envoyé : mardi, 17. octobre 2006 11:34
À : kennaim_at_gmail.com
Cc : oracle-l_at_freelists.org
Objet : Re: should one use ANSI join syntax when writing an Oracle application?

On 10/17/06, Ken Naim <kennaim_at_gmail.com> wrote:

	I find the ansi syntax verbose and agree with the poster who said that while
	it highlights the joins it obfuscates the tables which I find more important
	while debugging data issues. I don't understand why we need the ansi syntax 
	to separate the join and filter conditions, I have been doing it for years
	in the where clause, first the joins then the filter, look at any of my
	queries and it is plain as day even with 20 tables 6 inline views and 4 
	sub-queries.
	

as an exercise it might be illuminating to supply one such query to someone who is competent in sql, but unfamiliar with your work and ask them which tables are joined to which and how?

--

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

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 17 2006 - 16:28:27 CDT