RE: ANSI Joins

  • From: "Lex de Haan" <>
  • To: <alan.aschenbrenner@xxxxxxx>, <mgogala@xxxxxxxxxxxxxxxxxxxx>
  • Date: Tue, 18 Jan 2005 22:13:43 +0100

allow me to comment on terminology here: both flavors of the join syntax are
fully ANSI/ISO compliant.
in other words, there is *no* reason at all to use one over the other for
portability reasons --
with the exception of the (rather ugly) Oracle-specific outer join syntax
using the (+) operator.
I hope everyone agrees with me that the ANSI/ISO outer join syntax is much
more readable and less error prone.
everything else is a matter of taste ...

Tom Kyte Seminar:

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of alan.aschenbrenner@xxxxxxx
Sent: Tuesday, January 18, 2005 22:03
To: mgogala@xxxxxxxxxxxxxxxxxxxx
Cc: 'Oracle-L (E-mail)'; oracle-l-bounce@xxxxxxxxxxxxx
Subject: Re: ANSI Joins


    I also dislike ANSI syntax.  Besides it being difficult to read, the
optimizer sometimes does crazy things with ANSI syntax (especially when
involving dblinks).  I have rewritten a few selects into non-ANSI syntax,
and the optimizer has chosen a MUCH better execution plan.  I have seen
queries run for minutes in ANSI syntax, but take only seconds when rewritten
in non-ANSI syntax.  Maybe the ANSI syntax query was written poorly, but not
being proficient in it, I couldn't make any suggestions...
(these observations were made with 9i databases)

Has anyone else had this experience?

If it is an optimizer problem, maybe this is fixed in the 10g optimizer.

My 2 cents...


Alan Aschenbrenner
Oracle DBA
Information Handling Services


Confidentiality Notice: The information in this e-mail may be confidential
and / or privileged. This e-mail is intended to be reviewed by only the
individual or organization named in the e-mail address. If you are not the
intended recipient, you are hereby notified that any review, dissemination
or copying of this e-mail and attachments, if any, or the information
contained herein, is strictly prohibited.


                      Mladen Gogala

                      <mgogala@allegients        To:       "'Oracle-L
(E-mail)'" <oracle-l@xxxxxxxxxxxxx>                                
            >                cc:

                      Sent by:                   Subject:  ANSI Joins





                      01/18/2005 11:45 AM

                      Please respond to




My developers are starting to use ANSI joins in vain hope that they will
make their apps portable across databases. I have a positive attitude toward
ANSI joins: I hate those verbose extensions that make SQL statements lengthy
and unreadable. What is the opinion of other people about ANSI joins?
What is the @#$%! allure of those things? Where did they learn it from?