Re: should one use ANSI join syntax when writing an Oracle application?
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: "Mladen Gogala" <mgogala@xxxxxxxxxxx>
- Date: Wed, 18 Oct 2006 06:37:24 +0100
On 10/18/06, Mladen Gogala <mgogala@xxxxxxxxxxx> 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
Other related posts:
