Re: mix ANSI and Oracle JOINs?

  • From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
  • To: jkstill@xxxxxxxxx
  • Date: Thu, 23 Dec 2010 12:09:41 -0600

I believe you wrote a whole chapter on Subquery Factoring in a recently 
released Apress book, right? ;)

I have to say that I find ANSI join syntax considerably less clear in its 
intent. I also seem to see more errors with it than with the traditional Oracle 
join syntax. I'm not sure whether that's due to the syntax itself or the 
developers that tend to use it. But I do think the fact that it is less 
straight forward contributes to the mistakes. I'm not sure Oracle even directly 
supports mixing the syntax by the way (I would not want to combine them, even 
if it is documented). I actually saw an example this week. It was a very simple 
statement, so the error is easy to spot. But in more complicated examples with 
many tables and many join 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 =,  table_3 c
where ...

Unfortunately, table_3 had about 40 million records. The query ran for a few 
hours before it got killed.

Kerry Osborne

On Dec 23, 2010, at 11:38 AM, Jared Still wrote:

> On Thu, Dec 23, 2010 at 8:08 AM, Kenneth Naim <kennethnaim@xxxxxxxxx> wrote:
> I also find the ansi syntax difficult to read and format when
> inline views are present.
> this is easily rectified by using query subfactoring  (WITH clause) rather 
> than
> inline views.
> I've never like inline views simply because they make the queries so much
> more difficult to understand.
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog:
> Home Page:

Other related posts: