Re: mix ANSI and Oracle JOINs?
Date: Fri, 24 Dec 2010 12:46:00 -0800
Message-ID: <AANLkTikhX54rr4gAZs39j5BSOpzSQRerSDkJav0rxMZR_at_mail.gmail.com>
On Thu, Dec 23, 2010 at 10:09 AM, Kerry Osborne <kerry.osborne_at_enkitec.com>wrote:
> I believe you wrote a whole chapter on Subquery Factoring in a recently
> released Apress book, right? ;)
>
Why yes, yes I did. :)
And just yesterday I received my complimentary copies. The effort of writing (and rewriting) seems more worthwhile when the results are seen in official print. :)
>
> I have to say that I find ANSI join syntax considerably less clear in its
> intent.
>
I felt that way at first, but I forced myself to start using it rather than the original Oracle syntax.
There are several reasons for this, some I can recall at the moment:
- I have to read other people's SQL, and they may be using ANSI joins
- As I also at times work with SQL Server, and it uses ANSI joins, it seemed a good idea to learn the syntax
- Personally, I have almost never, if ever, put the (+) on the correct table the first try in an outer join (oracle syntax)
- There may be new features available in the Oracle future that are ANSI only (speculation on my part)
- I don't want to admit at a party that I don't know ANSI join syntax. (though many will question whether a party is worth attending if SQL join syntax will be discussed)
- and finally, I using ANSI join syntax for some time, I know longer have to think about it too much.
> conditions it can be difficult to locate the problem. Here's what I saw
> this week (cleaned up to protect the guilty):
>
> select ...
> from table_1 a left join table_2 b on a.name = b.name, table_3 c
> where ...
>
>
Well, that is just wrong.
It would be interesting to see how the CBO may have transformed that SQL.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 24 2010 - 14:46:00 CST