Re: ANSI Joins

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: mgogala@xxxxxxxxxxxxxxxxxxxx
  • Date: Tue, 18 Jan 2005 20:32:31 +0100

I guess that the (laudable) idea besides ANSI joins is to help people 
distinguish between *pure* filtering conditions and join conditions, 
with the no less laudable idea of making forgetting a join condition in 
a 25-way join more difficult.
In practice nothing prevents you from putting filtering conditions where 
join conditions are expected (I even have seen it advised as a 'tuning 
trick' with DB2) - except with IS NULL conditions on outer joins, 
because then it doesn't work - and when you have to join on several 
columns you can still forget one.
As far as I am concerned, I dislike the ANSI syntax because a table 
looks more equal than the others. It may well be the driving table, but 
not necessarily, and it may give you a totally distorted mental image of 
what the optimizer does in your back. It's not necessarily because most 
of your columns in the SELECT list come from one table that you must 
consider it the driving table. Somehow, I like what I write to be (more 
or less) in line with what is executed. But it's just my personal taste.

My 0.02 euros.


Mladen Gogala wrote:

>My developers are starting to use ANSI joins in vain hope that they will 
>make their apps portable
>across databases. I have a positive attitude toward ANSI joins: I hate 
>those verbose extensions
>that make SQL statements lengthy and unreadable. What is the opinion of 
>other people about ANSI joins?
>What is the @#$%! allure of those things? Where did they learn it from? 
>Is there any readable document
>that explains  ANSI joins for dummies?


Other related posts: