Home » SQL & PL/SQL » SQL & PL/SQL » natural join change table
natural join change table [message #660267] Mon, 13 February 2017 20:24 Go to next message
asliyanage
Messages: 60
Registered: January 2017
Member
when i am using natural join with two table i can change the table position as below.

1) select *
from DEPARTMENTS natural join locations;

2)select *
from locations natural join DEPARTMENTS;

I need to know is there any differences between above two times?
natural join [message #660268 is a reply to message #660267] Mon, 13 February 2017 20:35 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
when we use natural join we can't use the join column in select clause as below.

1)select l.location_id,postal_code,state_province,city
from locations l natural join departments;

But we can use location_id column as below

2)select LOCATION_ID,postal_code,state_province,CITY
from locations l natural join departments;

I need to know what is the different between 1 and 2. both use location_id column.
Re: natural join change table [message #660269 is a reply to message #660267] Mon, 13 February 2017 20:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
asliyanage wrote on Mon, 13 February 2017 18:24
when i am using natural join with two table i can change the table position as below.

1) select *
from DEPARTMENTS natural join locations;

2)select *
from locations natural join DEPARTMENTS;

I need to know is there any differences between above two times?
what prevents you from issuing both statements & inspecting the results?
post EXPLAIN PLAN from both statements.

Just because you can do something (use NATURAL JOIN) , it does not means that you actually should ever do it.
You can also poke yourself in the eye with a sharp pencil, but you are advise to never do either action.
Re: natural join change table [message #660271 is a reply to message #660267] Tue, 14 February 2017 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide.

You did NEVER feedback and thank people who helped you, why?
Review your previous topics and do it now.

Re: natural join change table [message #660272 is a reply to message #660271] Tue, 14 February 2017 01:40 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
my problem still exists. I didn't get proper answer yet.
Re: natural join change table [message #660277 is a reply to message #660272] Tue, 14 February 2017 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

FIRST review your previous topics and feedback and thank people who spent time to help you instead of demanding help, we owe you nothing, yet.

Re: natural join change table [message #660289 is a reply to message #660277] Tue, 14 February 2017 03:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
They are the same. The only join type where the table order makes a difference is LEFT and RIGHT OUTER JOIN.

That said - never, ever use natural join. It's one of the dumbest things ever added to SQL.

Try adding a time_updated column to both tables and see what happens.
Re: natural join change table [message #660320 is a reply to message #660289] Tue, 14 February 2017 15:41 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I am agreeing with cookiemonster. NO developer would ever use a NATURAL JOIN in any production code. It is guaranteed that It will cause a problem in the future. If I had a developer that used a natural join in anything but an adhoc query, the first time would be a warning, the second time would be a termination.

If there was a database setting that I could set to stop them from working I would immediatly turn it on. Lets take an example. We have 2 tables with the following structure

table1(col1, col2,col3)
table2(col1,col4,col5)

A natural join would join them only on col1. Now I need a new column in table1 and I decided that I wanted to call it col4 my natural join immediatly fails. If I had a normal join nothing breaks. NEVER USE NATURAL JOINS.
Previous Topic: REGEXP to find the comma position
Next Topic: Problem while using AUTHID CURRENT_USER
Goto Forum:
  


Current Time: Fri Mar 29 07:55:09 CDT 2024