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

On 10/18/06, Mladen Gogala <mgogala_at_verizon.net> wrote:

Niall, fortunately not all joins are outer joins. What bothers me the most
> are queries like this:

only the ones I get to look at :(

select a,b,c from A left outer join B left outer join C left outer join D

I agree that that is awful, though I've seen/written enough select a,b,c from A,B,C in my time which is at least as bad.

or, worse,
> select a,b,c from (
> select * from A left outer join B left outer join C left outer join D
> ) where <conditions on a,b and c>;

Is that any worse than

select a,b,c from (
select * from a,b, c where ..... )
where <conditions on a,b,c)?

The latter query will retrieve all columns from the tables A,B,C and D,
> which can be overwhelming. You are a well known consultant and a respected
> figure in the database world and you know how to write a good query.

That's very kind of you, though I'm not a consultant - I'm a DBA (hey maybe I can start to say I'm an apps DBA now as well :( ).

Some
> developers, especially younger developers (pun intended) write lousy
> queries
> like the latter one. That is what motivates me for my jihad against ANSI
> joins.

To be honest an awful lot of the SQL I see is not written but generated, but I know what you mean, I don't myself see that ANSI syntax itself drives bad queries - I'd hold the developer or dba that wrote it responsible. On the other hand it's probably about time that a jihad against a type of disk layout was joined by a jihad against something else and sql syntax seems a reasonable target - me I'd be crusading against J2EE but there you go.

--


> Mladen Gogala

> http://www.mladen-gogala.com

>

>



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

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