Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vic.

Asked: March 31, 2009 - 4:36 am UTC

Answered by: Tom Kyte - Last updated: October 15, 2012 - 10:29 am UTC

Category: Database - Version: 11.1.0.7

Viewed 1000+ times

You Asked

Hi Tom,

I have seen lots of information on the internet etc about the usage of ANSI SQL, however on this website I see you saying something like 'don't use the natural join feature'.

Would you recommend not using the ANSI SQL at all, and sticking with the 'traditional' way using (+) or just not using the natural join feature.

Many thanks

Vic


and we said...

Natural join uses the column names, you don't specify anything.

It is totally error prone for that reason.


ops$tkyte%ORA10GR2> create table t1 ( x int primary key, y int, z int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( x int primary key, y references t1, a int );

Table created.

<b>obviously, to join t1 to t2, you would use t1.x = t2.y, the primary/foreign key...</b>


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values ( 1, 1 , 100);

1 row created.

ops$tkyte%ORA10GR2> insert into t1 values ( 2, 2 , 200);

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t2 values ( 1,  1 , 300);

1 row created.

ops$tkyte%ORA10GR2> insert into t2 values ( -2, 1 , 400);

1 row created.

ops$tkyte%ORA10GR2> insert into t2 values ( 2,  2 , 500);

1 row created.

ops$tkyte%ORA10GR2> insert into t2 values ( -4, 2 , 600);

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t1 natural join t2;

         X          Y          Z          A
---------- ---------- ---------- ----------
         1          1        100        300
         2          2        200        500

<b>Hmmm..... </b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t1 join t2 on (t1.x = t2.y);

         X          Y          Z          X          Y          A
---------- ---------- ---------- ---------- ---------- ----------
         1          1        100          1          1        300
         1          1        100         -2          1        400
         2          2        200          2          2        500
         2          2        200         -4          2        600

<b>oh, there we go</b>




You want to be specific, use the ANSI join if you like/want

Just do not use 'natural' join, you WANT to use column names explicitly.

and you rated our response

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Thanks

April 01, 2009 - 3:24 am UTC

Reviewer: Vic Cordiner from London

Hi Tom,

Many thanks for that, as ever a very good answer as well as the demo explaining your answers.

Vic

Oracle has officially declared (+) deprecated

October 14, 2012 - 10:43 am UTC

Reviewer: Prakash Rai

Hi Tom -

Referring to text from Seteve O'Hearn's SQL Certified Expert book "The famous - or infamous - plus sign, a longtime friend of many veteran Oracle professionals, is on its way out the door. It stilll works. But Oracle Corporation is in the process of kicking it to the curb. Oracle Corportation has officially declared it deprecated."

- Is there a timeline Outer Join Syntax (+) will no longer supported?

- Since the syntax has been deprecated, do you recommand ANSI SQL for the development or even modify existing code to ANSI SQL?

Thanks as always for your direction!
Prakash
Tom Kyte

Followup  

October 15, 2012 - 10:29 am UTC

no timeline, it'll probably be there forever. LONG has been deprecated for as long as I can remember now :)

I would say that if it is officially deprecated - you should consider the ansi syntax going forward. As you modify old code - see about fixing up occurrences as you hit it - but I wouldn't go tearing through my existing code to "fix" it.

Oracle has officially declared (+) deprecated

October 17, 2012 - 8:52 pm UTC

Reviewer: Prakash Rai from Denver CO

That sounds reasonable to me. Deprecated LONG is a good example. Bottom line use ANSI SQL and modify existing code as time allows.

Thanks
Prakash