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
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@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
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
