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.|
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?|
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.