aspose file tools*
The moose likes Oracle/OAS and the fly likes Natural Join when no common column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Natural Join when no common column" Watch "Natural Join when no common column" New topic
Author

Natural Join when no common column

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 249




It gives me this

STU_ID STU_NAME BRANCH INS_ID DEPT
111 A CS 123 CS
222 B IT 123 CS
111 A CS 124 IT
222 B IT 124 IT

But this is Cross join output. And Natural join is inner join.
Why it does not give error as there is no matching column


Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer
Jelle Klap
Bartender

Joined: Mar 10, 2008
Posts: 1760
    
    7

A natural join is not by definition an inner join. It is by default, but it can also be a left/right outer join.
As there is no natural join condition in this case what you get is a Cartesian product, as expected.


Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 249

I created another table


ORA-25155: column used in NATURAL join cannot have qualifier

Oracle Docs , An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to every column of that table that is not a common column.


select students.*,instructors.*
from students natural join instructors;

This works .

Jelle Klap
Bartender

Joined: Mar 10, 2008
Posts: 1760
    
    7

The natural join between students and instructors works because it is identical to the select statement in your orignal post. The natural join between students and classes does not work because you are qualifying the shared column branch with a specific table, once for the students table, and once for the classes table. Oracle doesn't allow that.
 
Don't get me started about those stupid light bulbs.
 
subject: Natural Join when no common column