Home » SQL & PL/SQL » SQL & PL/SQL » natural join
natural join [message #323188] Tue, 27 May 2008 11:07 Go to next message
nchaurasia
Messages: 2
Registered: May 2008
Junior Member
I am using natural join on the following tables.

ORA_USER1
(
NAME VARCHAR2(25 BYTE),
AGE INTEGER
)

ORA_PERSON
(
PERSON_ID NUMBER(3),
FIRST_NAME VARCHAR2(25 BYTE),
LAST_NAME VARCHAR2(25 BYTE),
)

They do not have any column with same column name.

When i execute the following query
select * from ora_user1 natural join ora_person
i am getting valid output data.

Accoring to the defintion of natural join

The NATURAL JOIN performs a join for all columns with matching names in the two tables. This one is a very useful syntax enhancement because it removes the need to add a WHERE clause for the table join conditions, and make all entries in the WHERE clause only used for result set filtering. By the way, Oracle9i assumes a join on the columns that have the same name,


Am i doing something wrong. Pls correct me.

[Updated on: Tue, 27 May 2008 11:08]

Report message to a moderator

Re: natural join [message #323189 is a reply to message #323188] Tue, 27 May 2008 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As there are no column with same name, you just make a cartesian product.

NEVER use natural join. ALWAYS specify on which columns you want to join.
This will prevent you from this kind of errors and will avoid many hours of headache searching where the error might be in your queries.

Regards
Michel
Re: natural join [message #323231 is a reply to message #323189] Tue, 27 May 2008 18:26 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
I agree with Michel and just wanted to add, per
your comment:

i am getting valid output data. 


You are getting output - but it is not valid if your intent
was to correlate the data somehow.

Read up on the cartesian product - you are getting
a multiplication product of the sets. ie, for each
row in table 1 you are getting all rows in table 2.

If table 1 has 100 rows and table 2, a 1000 then your
results will be 100,000.

I stress this because when one works with much larger joins
it is often easy to forget to put in one of the join fields
and if you dont catch this in the EXPLAIN PLAN output
this too will lead to debug city (on an endless query).

Name the fields = safety,clarity & precision!

Best Regards,
Harry
Re: natural join [message #323267 is a reply to message #323188] Wed, 28 May 2008 00:14 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you are learning SQL, and you're not going for cerfification, then I have a word of advice: Skip natural joins. They are BS, and should never be used in production systems
Previous Topic: Help in Pipelined function to extract records
Next Topic: job scheduling
Goto Forum:
  


Current Time: Fri Dec 02 23:17:34 CST 2016

Total time taken to generate the page: 0.09024 seconds