Tony -- Thanks for the question regarding "Oracle support of ANSI joins.", version 9.2

You Asked

Hi Tom,
While looking into the documents of 9i, I found that Oracle now supports ANSI joins. 
Since Oracle optimizer uses where clause to draw the execution plan (cost etc.), how it 
does with new natural joins? Is it just another layer on the top of existing Oracle's way 
of joins? I was wondering that if this not the case, then did Oracle change its Kernel in 
9i to accommodate this. I am curious to know more details about this. Any help in this 
regard will be highly appreciated.
Regards, 

and we said...

natural joins -- a bug waiting to happen -- are done exactly the same way as they were 
when you specified the join conditions yourself.  There is no difference there, just the 
syntax is different.  We look up in both tables for all of the common column names and 
build the where clause for you


scott@ORA920> select ename, dname from emp natural join dept;
 
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=14 Bytes=252)
   1    0   HASH JOIN (Cost=6 Card=14 Bytes=252)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=4 Bytes=44)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=14 Bytes=98)
 
 
 
scott@ORA920> select ename, dname from emp, dept
  2  where emp.deptno = dept.deptno;
 
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=14 Bytes=252)
   1    0   HASH JOIN (Cost=6 Card=14 Bytes=252)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=4 Bytes=44)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=14 Bytes=98)
 


i strongly, vehemently strongly, discourage the use of this "feature" called natural 
joins

to join tables by their colum names -- how patently "just a bad idea".  We've bugs logged 
saying "hey, this natural join returns the wrong answer" -- but wait -- they were 
comparing it to what they THOUGHT should have been the query, not with what the query 
really way.  Consider a model like this:


create table emp ( empno, deptno, ename );
create table dept ( deptno, dname, empno /* person responsible for this dept */ );


now, generate a list of enames and the dname they work in using a natural join, whoops.  


Never never never use this "feature"

 

Reviews

"Oracle support of ANSI joins.", version 9.2   November 3, 2003 - 3am Central time zone Bookmark | Bottom | Top Reviewer: pete moss from uk, staffordshire university Tom - totally agree with you about natural joins, a really stupid idea! A very important fact to note for anyone who is contemplating using ANSI join syntax in a new application is to note the fact that forms 9i does NOT support the new syntax! We made the assumption that the new release of forms would support the new features of the database only to discover, to the setback of our project, that it will not compile ANSI style joins - crazy I know, and we had trouble getting oracle support to believe us on this - but trust me on this, we now have a proper bug number and everything ;) pete :) But surely   November 3, 2003 - 7am Central time zone Bookmark | Bottom | Top Reviewer: Fred Funk from UK "to the setback of our project" You would have noticed this the first time you attempted to compile the form ! Forms is always a few steps behind in the versions of PL/SQL - it would be almost impossible for it to keep up. well, yes!   November 3, 2003 - 8am Central time zone Bookmark | Bottom | Top Reviewer: pete moss from uk As you point out, we did spot this straight away - but we had been planning all along to use ANSI style joins (going so far as getting training for developers who did not know the ansi syntax!), which meant that we had to start developing our system knowing that we will, at some point - once it is supported, go back and retro-code loads of queries into that style, if we want to have all sql through the system to be consistent. which is a pain, to say the least! hey ho. Followup   November 3, 2003 - 2pm Central time zone: well, i'm not going back and changing my decodes to case, nor would I ever rewrite the old style joins to ansi. but anyway. in forms however, how often do you really join? and why isn't this code in STORED PROCEDURES (if it is doing data stuff)? forms is pretty much "default block" stuff or built on stored procedures.... and the difference between: from emp inner join dept on (emp.deptno = dept.deptno) vs from emp, dept where emp.deptno = dept.deptno hopefully didn't take a fully day or training? Forms SQL and PL/SQL   November 3, 2003 - 7pm Central time zone Bookmark | Bottom | Top Reviewer: Gary from Sydney, Australia Forms is quite happy with ANSI joins as part of the definition of a block based on a where clause or a record group. The issue is with Forms' PL/SQL. I'm using Forms 6i, which states "This release of Form Builder uses PL/SQL 8 on the client and the server." But Oracle version numbering makes it difficult to work out what equates to what. Stick to either procedures in the database, or use dynamic SQL in the forms. That way you can always gain the advantage of migration to later database versions without forcing a migration of your forms versions. PS. I also agree that NATURAL joins are a mistake. Just have those often standard 'CREATED_BY', 'CREATED_ON' columns on your tables and watch the lack of results. Maybe we could petition Oracle for a parameter for turning it off. ANSI reference   March 28, 2007 - 5pm Central time zone Bookmark | Bottom | Top Reviewer: A reader  Hi Tom, My apologies if I submitted the question again, as there were some interruption when I pressed "Submit Review" button. Is there any complete reference book available to know which SQL command/syntax is ANSI standard? For example, I though MINUS will be an ANSI standard, but I came to know that actually it is not :-) Regards, Followup   March 30, 2007 - 11am Central time zone: you can order the ansi standards from ansi http://ansi.org/ however, they are rather "dry" and somewhat meaningless. Buggy?   February 19, 2008 - 11am Central time zone Bookmark | Bottom | Top Reviewer: Allan from Indiana, USA Historically there have been bugs related to ANSI syntax, in fact even the 10.2.0.4 projected issues list includes 10 bugs/issues related to ANSI syntax. In the past I've encountered some of these bugs myself, and have continued to use and advocate the "traditional" Oracle style. I'd like to know if you feel that the implementation of ANSI syntax is now equally robust compared to the traditional syntax. Followup   February 19, 2008 - 5pm Central time zone: unfortunately, there are bugs in non-ansi joins too, probably more than 10 in fact. I personally do not use the new syntax (except in the rare case of a full outer join, a truly rare beast to encounter). I have no comment on it really. ANSI syntax useful for outer joins   February 19, 2008 - 11pm Central time zone Bookmark | Bottom | Top Reviewer: A reader  I've found the ANSI syntax useful for outer joins, in that it allows a table to be outer joined to multiple other tables, whereas the join operator (+) does not. Followup   February 20, 2008 - 8am Central time zone: inline views - i like them for that myself.

Write a Review