RE: should one use ANSI join syntax when writing an Oracle application?
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Oct 2006 15:32:04 +0200
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@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?
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@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
--
//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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
