ODC Appreciation Day: Why I like ANSI-Joins #ThanksODC

von Sabine Heimsath (Kommentare: 0)

This article is part of series that used to be called OTN Appreciation Day. (Oracle Tech Net (OTN) recently changed its name to Oracle Developer Community (ODC)).
The Appreciation Day's purpose is to say 'thank you' for the wealth of help and information we received for so many years by writing an article about an Oracle-related topic. You will find the other articles on Twitter and on the initiator's blog.

The ANSI Join

I know this is a dangerous area, because it touches some people's comfort zone. But since I left my own and forced myself to switch from Oracle join syntax to ANSI syntax I am completely convinced that it should be used more widely.
Heart SQL


Let's look at a short example query.
Obviously, the programmer was not very disciplined when writing this - join conditions and where conditions are all over the place.
This is something that could be overcome with a little discipline...

select *    
from hr.employees e,     
     hr.jobs j,    
     hr.locations l,     
     hr.job_history h,     
     hr.regions r,     
     hr.departments d,    
     hr.countries c    
where e.job_id = j.job_id    
  and d.location_id = l.location_id
  and j.job_id = 'AD_ASST'
  and c.country_id = l.country_id
  and c.country_id = 'US'
  and d.department_id = e.department_id
  and c.region_id = r.region_id
  and h.employee_id = e.employee_id

... or by using a syntax that doesn't allow this mess in the first place:

select *
 from hr.employees e  
 join hr.jobs j on e.job_id = j.job_id
 join hr.job_history h on h.employee_id = e.employee_id
 join hr.departments d on d.department_id = e.department_id
 join hr.locations l on d.location_id = l.location_id
 join hr.countries c on c.country_id = l.country_id  
 join hr.regions r on c.region_id = r.region_id
where j.job_id = 'AD_ASST' 
  and c.country_id = 'US'

In the ANSI example you can distinguish the join and the where conditions at a glance.

Full outer join

Another big advantage, of course, is the possibility of having a full outer join with ANSI syntax.
I don't know why Oracle never introduced their own syntax for it, but this is definitely an argument in favour of ANSI syntax.


Do you remember when learning outer join syntax? I used to have a hard time remembering where the (+) had to go.
On the other hand, people are complaining about confusing ANSI's 'left outer' and 'right outer' join. So let's call it a draw, ok?

Tip: When I started to switch to ANSI I concentrated on 'left outer' and I hardly ever need the other one.


I summarised the most common arguments for both choices in this little table:

 Why am I using ANSI-Joins? Why am I using Oracle Joins?
  • I prefer clarity
    (where conditions and join condition are clearly separated)
  • I have enough discipline (and my colleagues, too) OR
    I never lose track even in the biggest SQL statement
  • I need full outer joins
  • I am used to ANSI syntax
  • I am used to Oracle syntax
  • I circumvent the problem that (+) is replaced by an emoticon in some forums ;-)

So - besides your desire to stay within you comfort zone - what keeps you from switching to ANSI?


There used to be problems with query rewrite that inspired this little Haiku:

I don't know if these problems are still around, so be aware and check if your materialized views are affected.


Einen Kommentar schreiben