Intro to DBMS

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 the other as a 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 the above query is given under

Concept: Natural join is a select condition and cross-product.

How Does 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 skips 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 the above diagram, and then the result is given below.

Keep in mind: If similar results are required as in Natural Join, but common columns do not hold the same names, then the following SQL query 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 tables above the SQL query.

  • After Where Clause (Student.Std_ID= Std_Marks.Std_ID;) is a condition.