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

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 17 Oct 2006 14:18:11 +0100
Message-ID: <7765c8970610170618j2aea7deekbd362e9c6173d4b4@mail.gmail.com>

On 10/17/06, Mladen Gogala <mgogala_at_verizon.net> wrote:
>
>
> 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?

it seems to me that where emp.deptno = dept.deptno is an entirely different fish from where emp.deptno=10, in the former case you are saying how employees and departments are related, in the latter you are specifying what criteria you are using to choose data.

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

I don't understand what you mean here, my naive example is below, where are all the columns involved?

SCOTT @ nl102 >select e.ename,d.dname
  2 from
  3 emp e left outer join dept d
  4 on (e.deptno=d.deptno)
  5 ;

ENAME DNAME
---------- --------------

MILLER     ACCOUNTING
KING       ACCOUNTING
CLARK      ACCOUNTING
FORD       RESEARCH
ADAMS      RESEARCH
SCOTT      RESEARCH
JONES      RESEARCH
SMITH      RESEARCH
JAMES      SALES
TURNER     SALES
BLAKE      SALES
MARTIN     SALES
WARD       SALES
ALLEN      SALES

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

That's ok there's always analytic functions that are a cunning plan to make SQL amazingly efficient and unreadable all at once :)

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

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

Received on Tue Oct 17 2006 - 08:18:11 CDT