Difference Between Inner Join and Natural Join

The key difference between Inner Join and Natural Join is that Inner Join provides the result based on the matched data according to the equality condition specified in the query while Natural Join provides the result based on the column with the same name and same data type present in tables to be joined.

DBMS allows to easily store, retrieve and manipulate data. It stores data in the form of tables. Each table consists of rows and columns. The rows represent each entity while the columns represent the attributes. Assume a Student database. Each row represents a student. The columns represent the attributes such as id, name, grade, age. A DBMS is a collection of tables and each table is related using constraints such as foreign keys. Sometimes it is not enough to use a single table. There are situations that require using multiple tables. To combine two tables, at least one column should be common. The combination of tables is called join.

CONTENTS

1. Overview and Key Difference
2. What is Inner Join
3. What is Natural Join
4. Relationship Between Inner Join and Natural Join
5. Side by Side Comparison – Inner Join vs Natural Join in Tabular Form
6. Summary

What is Inner Join?

Example for an inner join is as follows. Below is the student table.

The student_info table is as follows.

To perform inner join, there should be at least one match between both tables. The id 1,2,3 are common to both tables. Therefore, it is possible to perform inner join.

The INNER JOIN query to join these two tables is as follows.

SELECT * from student

INNER JOIN student_info WHERE student.id = student_info.id;

Executing the above SQL command will output the following table.

What is Natural Join?

Example for a natural join is as follows. Below is the student table.

The student_info table is as follows.

To perform natural join, there should be a column with the same name and same data type. The id column is the same for both tables. Therefore, it is possible to natural join both these tables.

The NATURAL JOIN query to join these two tables is as follows.

SELECT * from student NATURAL JOIN student_info;

Executing the above SQL command will output the following table.

What is the Relationship Between Inner Join and Natural Join?

• Natural Join is a type of Inner Join.

What is the Difference Between Inner Join and Natural Join?

Inner join provides the result based on the matched data according to the equality condition specified in the query while the natural Join provides the result based on the column with the same name and same data type present in tables to be joined. Moreover, the syntax of inner join and natural join are different.

When the table1 consist of id, name, and table2 consist of id and city, then the inner join will give the resulting table with matching rows. It will have id, name, again id and city. On the other hand, in natural join, will give the resulting table with matching rows with columns id, name, city.

Summary – Inner Join vs Natural Join

The key difference between inner join and natural join is that inner join provides the result based on the matched data according to the equality condition specified in the SQL query while natural Join provides the result based on the column with the same name and same data type present in tables to be joined.

Reference:

1.Tutorials Point. “SQL INNER JOINS.”  Tutorials Point, 8 Jan. 2018. Available here

Image Courtesy:

1.’SQL Join – 07 A Inner Join B’By GermanX – Own work, (CC BY-SA 4.0) via Commons Wikimedia