I share your aversion to the natural join (even though that name has a resonance with me for other reasons), but ANSI syntax != natural join. I'm not a huge fan of layers upon layers of views - but that doesn't make views bad.
I will rather agree with Mladen. I use the dba-base link and the NATURAL join it is all but natural:
AnsiSQL: SELECT department_name, city FROM departments NATURAL JOIN locations;
Oracle: SELECT d.department_name, l.city FROM departments d, locations l WHERE d.location_id = l.location_id AND d.country = l.country;
The problem with NATURAL is that the join columns become hidden. SQL was a language to express you thought. It becomes here with hidden thought. So by looking at the Ansi syntax I have no idea of the joins. I give you 2 tables with 200+ columns and you have to search, not difficult but not natural.
As of the verbose side ansisql, it as bad and good:
Bad : when you have 20 joins, the list prevent you to have an overview of what is happening. I always found big queries ansi SQL to be quite unfriendly. The Oracle way is more 'schematic'.
Good: It becomes harder on small query to make a cartesian join. With the oracle way, you can quickly forget a predicate. The predicate is in your head, not on the sql, and result is cartesian product.
Having said that, the last tendency of analytic function violate 100% the philosophy of SQL which was keep the syntax simple and leave difficulty on the action performed. Now if I don't practise the rollup, I tend to forget the syntax.
-----Original Message----- From: Ashton Anthony (Mr A) EDU [mailto:Anthony.Ashton@xxxxxxxxxxxxxx] Sent: Tuesday, 17 October, 2006 2:54 PM To: mgogala@xxxxxxxxxxx Cc: oracle-l Subject: RE: should one use ANSI join syntax when writing an Oracle application?
Don't be resistant to change - Appreciate the differences and argue for/against without using emotive language. Many people (especially younger developers) will find the ANSI syntax easier to read and understand.
-----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mladen Gogala Sent: 17 October 2006 13:36 To: niall.litchfield@xxxxxxxxx Cc: Jacques.Kilchoer@xxxxxxxxx; oracle-l Subject: Re: should one use ANSI join syntax when writing an Oracle application?
On 10/17/2006 01:43:14 AM, Niall Litchfield wrote: > My personal preference is for the ANSI standard syntax, I really find > that seperating out the joins from the other conditions helps me > understand more easily what a query is trying to achieve. It also helps debugging IMO.
How, exactly are joins different, in your opinion? . > > So I'd say that by and large my experience has been positive and that > I find the style a lot easier to understand.
SQL is modeled after naive set theory. The most basic goal of SQL is to help users define what to select from where. SQL can be described as "Venn diagrams meet spreadsheets". In order to achieve that, one should specify columns being selected and conditions that must be met in order for the row to qualify for the desired subset. My greatest beef with ANSI joins is precisely the fact that all the columns from both tables are included in the join. The language of ANSI join is an idiotization of the standard SQL. Whoever came up with that deserves to die slow and horrible death. Probably, someone has had a cunning plan how to make SQL better. Result is what I call a Baldrick addendum to the standard SQL.
-- Mladen Gogala http://www.mladen-gogala.com
PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET. On entering the GSI, this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs. In case of problems, please call your organisational IT Helpdesk. The MessageLabs Anti Virus Service is the first managed service to achieve the CSIA Claims Tested Mark (CCTM Certificate Number 2006/04/0007), the UK Government quality mark initiative for information security products and services. For more information about this please visit www.cctmark.gov.uk
The original of this email was scanned for viruses by Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs. On leaving the GSI this email was certified virus free. The MessageLabs Anti Virus Service is the first managed service to achieve the CSIA Claims Tested Mark (CCTM Certificate Number 2006/04/0007), the UK Government quality mark initiative for information security products and services. For more information about this please visit www.cctmark.gov.uk -- http://www.freelists.org/webpage/oracle-l
-- Niall Litchfield Oracle DBA http://www.orawin.info