Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Natural Join when no common column

 
Mahtab Alam
Ranch Hand
Posts: 391
1
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator



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
 
Jelle Klap
Bartender
Posts: 1952
7
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mahtab Alam
Ranch Hand
Posts: 391
1
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1952
7
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic