Natural Join

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.

Example:

Suppose the following two tables Student and Student_Marks

Table: Student

Table: Student_Marks

To get all the unique columns from student and student_marks tables, the following SQL statement can be used

SQL Code:

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?

Example

Requirement: Select STD_Name from both tables (Student and Student_Marks) where both tables have common Std_ID.

SQL Query:

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.