Natural Join when no common column

[Mahtab Alam]

create table students(stu_id number,stu_name varchar2(30),branch varchar2(30)); insert into students values(111,'A','CS'); insert into students values(222,'B','IT'); create table instructors(ins_id number,dept varchar2(30)); insert into instructors values(123,'CS'); insert into instructors values(124,'IT'); select * from students natural join instructors;

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]

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]

I created another table
create table classes(branch varchar2(30),classroom varchar2(30)); insert into classes values('CS','S12'); insert into classes values('IT,'S11'); select students.*,classes.* from students natural join classes;

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]

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.