Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: should one use ANSI join syntax when writing an Oracle application?

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

From: Mark W. Farnham <>
Date: Wed, 18 Oct 2006 09:24:41 -0400
Message-ID: <007801c6f2b8$c5e76ba0$0c00a8c0@Thing1>

As we rejoin the essential stream of consciousness kicked off by the late, great Dijkstra in his 1968 ACM article about the harmfulness of "GO TO" we need to recognize what became apparent in building lexical parsers to "repair" spaghetti code into structured code: You can write things that work well but which are very difficult to read and understand in pretty much any language system. With proper use "GO TO" can clearly present exactly the same functional logic as IF-THEN-ELSE, DO-WHILE, and CASE. Dijkstra's great point was that it was far too easy and common for "GO TO" to obfuscate the meaning and intent of a program, while the so-called "structured" language constructs promoted clarity. Many examples ensued (from both true believers in the "GO TO" and from code comedians) of structured language programs that were nearly impossible to read thereby "proving" that avoiding "GO TO" is harmful.

So I suggest that rather than addressing the quasi religious debate innocently set off by the OP, we focus on writing queries to be intentionally clear. If a query author starts with a clear idea of what the data sources are, what the join relationships are, and what the focusing filters are the usual result is an easily readable query. Chapters of books have been written on making your SQL readable. If you can more clearly state your intention with "ANSI" join syntax, please do so. Likewise, if you can state your intention more clearly with (+) <plus fill in any missing partners with nulls>, please do so.

The result will be more readable code for people who follow after you. Which syntax is "better" is dependent on both the language and the person using it. I hope that this notion can be separated from the cost of execution.

Below, MN's example below demonstrates the power and clarity of a language construct now available in "ANSI" (full outer join). I find it almost disappointing to have the COST attached, because the point should be how much more clear and concise the language of the query is. So in this particular case for this particular purpose, I stand in praise of the ANSI syntax. My guess is that it would not be too hard to do a non-outer equijoin and slap that together with the pair of not-in or minuses and thereby compete with ANSI's cost. Again, though, the COST competition is not the point. Further, Wolfgang's point is not to be lost that (I paraphrase what I think he's thinking) it is unfortunate when the architecture forces you into outer joins at all. Without debating whether a full outer join should ever be needed in a well designed system the ANSI syntax seems to most clearly state the query intent by a huge margin in this case. Of course Oracle could simply implement

select count(*) from
(select * from test1 t1, test2 t2
where t1.n (+) = t2.n (+));

as the same result set as  

select count(*) from
(select * from test1 t1 full outer join test2 t2 on t1.n = t2.n);

There is no ambiguity, but that syntax is not supported. Possibly it should be, but I wouldn't burn any influence I have to suggest enhancements on that one. My point here is that an intentionally missing feature (if I imagine correctly this was omitted primarily because it would hide the potentially horrible expense of how the engine of the time would have had to do that) in the (+) syntax is what makes ANSI the clear winner in this case. It might even be argued that having to type out the whole words "full outer join" is a good thing to make you realize what you are requiring.

Even when COST becomes a real issue in test when you profile the performance of an application, a query is identified as costly enough and frequently enough executed to be worth looking at, and the resources spent to accomplish the query appear to be out of whack with the amount of work it seems should be done, I recommend retaining the original query's clear statement of the intent as a comment if you need to carve up the query or apply hints to overcome the apparent inability of some current generation of CBO to get a better plan. (Yeah, there are a few presumptions in there, including that the expense of the query was not due to unclear thinking in the first place and that a clear iso-functional restatement "magically" gets a great plan "naturally.")

I've taken far too long to try to make this point: Learn to read both. Write in whichever one you can write in more effectively. This may vary from query to query and as the calendar moves forward. I would not even straight jacket the employees working on a single project to one or the other. If someone who writes exclusively in the "other" syntax can't understand your query, it probably needs improvement anyway.



-----Original Message-----
From: [] On Behalf Of Mindaugas Navickas
Sent: Tuesday, October 17, 2006 11:53 PM To:; 'oracle-l'
Cc: 'Mladen Gogala'; Subject: RE: should one use ANSI join syntax when writing an Oracle application?


I am afraid - I am not on "popular site of the table". Here I did a small test (Oracle

create table test1 as
with m as ( select /*+ materialize */ 1 from all_objects where rownum <= 1000)
select TRUNC(dbms_random.value(400000,1000000)) n,dbms_random.string('A',10) v
from m,m;
create table test2 as
with m as ( select /*+ materialize */ 1 from all_objects where rownum <= 1000)
select TRUNC(dbms_random.value(0,600000)) n,dbms_random.string('A',10) v from m,m;
create index i_test1 on test1(n);
create index i_test2 on test2(n);
dbms_stats.gather_table_stats('MNTEST','TEST1',method_opt=>'FOR ALL COLUMNS SIZE AUTO');
dbms_stats.gather_table_stats('MNTEST','TEST2',method_opt=>'FOR ALL COLUMNS SIZE AUTO');
select count(*) from
(select * from test1 t1 full outer join test2 t2 on t1.n = t2.n); -- COST by running autotrace = 6953

select count(*) from
(select * from test1 t1, test2 t2
where t1.n (+) = t2.n
select * from test1 t1, test2 t2
where t1.n = t2.n (+));
-- COST by running autotrace = 42375

Which syntax is more readable is matter of taste - isn't it?

Mindaugas Navickas

-----Original Message-----
From: [] On Behalf Of Wolfgang Breitling
Sent: October 17, 2006 10:55 PM
To: oracle-l
Cc: Mladen Gogala; Subject: Re: should one use ANSI join syntax when writing an Oracle application?

I am on Mladen's side (at least as far as this topic is concerned) and I prefer the "old", or rather "traditional" (sounds better, doesn't it) style. I don't share Niall's opinion that a join predicate is different from an "ordinary" predicate.
I must confess my complete ignorance re ANSI syntax but regarding your preference for the ANSI syntax because it separates the two types of predicates. How would you express the following with ANSI style join syntax:

select ... from A, B where A.x < B.y


select ... from A, B where A.x between B.y and B.z

or any of the other non-equi joins.

Provided it is possible I have the feeling that will be hardly as concise and clear as the traditional syntax.

As far as the subtopic of outer joins is concerned, I don't like them in either syntax. Technically they are not necessary and are just a shortcut for a union (all) of two sql - or three for a full outer join (shudder). Maybe if the shortcut didn't exist there would be fewer of them.

At 07:18 AM 10/17/2006, Niall Litchfield wrote:

On 10/17/06, Mladen Gogala <> 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

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


Wolfgang Breitling
Centrex Consulting Corporation


Received on Wed Oct 18 2006 - 08:24:41 CDT

Original text of this message