Re: mix ANSI and Oracle JOINs?
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- Date: Fri, 24 Dec 2010 12:46:00 -0800
On Thu, Dec 23, 2010 at 10:09 AM, Kerry Osborne
<kerry.osborne@xxxxxxxxxxx>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
Other related posts:
