Natural Join is the cross product of two tables where it finds and returns the matching tuples.
Condition: Common columns (one as a primary key and other as foreign key) for joining must have the same name.
Keep in mind: In natural join, columns with the same name of both joining tables will appear once only.
Suppose the following two tables Student and Student_Marks
To get all the unique columns from student and student_marks tables, the following SQL statement can be used
SELECT * FROM Student NATURAL JOIN Student_Marks;
Output for above query is qiven under
Concept: Natural join is a select condition and cross product.
How Natural Join Actually Work?
Requirement: Select STD_Name from both tables (Student and Student_Marks) where both tables have common Std_ID.
SELECT Std_Name FROM Student NATURAL JOIN Student_Marks;
1. First see Cross product of Student and Student_Marks
Common Tupples where Std_ID of Student table = Std_ID of Student_Marks table, is given below.
As we know natural Join skip the duplicate common columns. So, one Std_ID is skipped as given below
2. Now Apply Condition
According to Condition, Select Just Student Name from above diagram then the result is given below
Keep in Mind: If just similar results are required as in Natural join but common columns does not hold the same names then the following query of SQL can be used instead of Natural-Join.
SQL: SELECT Std_Name FROM Student, Student_Marks Where Student.Std_ID= Std_Marks.Std_ID;
Student, Student_Marks is a cross product of two tablesIn above SQL query
- After Where Clause (Student.Std_ID= Std_Marks.Std_ID;) is a condition.