Re: should one use ANSI join syntax when writing an Oracle application?

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.

Niall

On 10/17/06, Polarski, Bernard <Bernard.Polarski_at_atosorigin.com> wrote:
>
> 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.
>
> My 2c
>
> B. Polarski
>
>
>
> -----Original Message-----
> From: Ashton Anthony (Mr A) EDU [mailto:Anthony.Ashton_at_dti.gsi.gov.uk]
> Sent: Tuesday, 17 October, 2006 2:54 PM
> To: mgogala_at_verizon.net
> Cc: oracle-l
> Subject: RE: should one use ANSI join syntax when writing an Oracle
> application?
>
> Mladen,
>
> 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.
>
> http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php
>
> Anthony
> --
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mladen Gogala
> Sent: 17 October 2006 13:36
> To: niall.litchfield_at_gmail.com
> Cc: Jacques.Kilchoer_at_quest.com; 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> 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
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l