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

  • From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
  • To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 17 Oct 2006 14:28:27 -0700

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 

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la 
part de Niall Litchfield
Envoyé : mardi, 17. octobre 2006 11:34
À : kennaim@xxxxxxxxx
Cc : oracle-l@xxxxxxxxxxxxx
Objet : Re: should one use ANSI join syntax when writing an Oracle application?

On 10/17/06, Ken Naim <kennaim@xxxxxxxxx> wrote: 

        I find the ansi syntax verbose and agree with the poster who said that 
        it highlights the joins it obfuscates the tables which I find more 
        while debugging data issues. I don't understand why we need the ansi 
        to separate the join and filter conditions, I have been doing it for 
        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 

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 

Other related posts: