Difference between Natural join and Inner Join in SQL
Last Updated :
01 Sep, 2023
The join operation merges the two tables based on the same attribute name and their datatypes are known as Natural join Unlike INNER JOIN, which requires you to specify the columns and conditions for the join explicitly. In this article, we will also see the differences between them. Let’s start with Natural Join.
Example:
If you have two tables “Student” and “Student_Marks” and you want to retrieve a record of students who have got marks then we use INNER JOIN using a shared column like ”ROLL_No”.
NATURAL JOIN
Natural Join in SQL joins two tables based on the same attribute name and datatypes. The resulting table will contain all the attributes of both tables but keep only one copy of each common column.
Syntax
SELECT *
FROM table1 NATURAL JOIN table2;
Example
Consider the two tables given below the first one is the Student Table and the Second is the Student_Marks with the help of these two tables we are doing INNER and NATURAL JOINS operations on the table.
Creating Student Table Query
CREATE TABLE Student(
Roll_No INT PRIMARY KEY,
Name VARCHAR(50));
--Insert the value in the Student table
INSERT INTO Student VALUES
(1,'Mohit'),
(2,'Aman'),
(3,'Sachin'),
(4,'Manish');
Output
Student Table
Query for Marks Table
CREATE TABLE Student_Marks(
Roll_No INT PRIMARY KEY,
Marks INT);
INSERT INTO Student_marks VALUES
(2,82),
(3,96),
(4,39),
(5,98);
Output
Student_Marks Table
Example:
Query
SELECT *
FROM Student NATURAL JOIN Student_Marks;
Output
Table 1
INNER JOIN
Inner Join joins two table on the basis of the column which is explicitly specified in the ON clause. The resulting table will contain all the attributes from both the tables including common column also.
Syntax
SELECT *
FROM table1 INNER JOIN table2 ON table1.Column_Name = table2.Column_Name;
Query
Consider the above two tables and the query is given below:
SELECT *
FROM Student S INNER JOIN Student_Marks M ON S.Roll_No = M.Roll_No;
Output
Table 2
Natural JOIN vs INNER JOIN in SQL
Natural Join joins two tables based on same attribute name and datatypes. |
Inner Join joins two table on the basis of the column which is explicitly specified in the ON clause. |
In Natural Join, The resulting table will contain all the attributes of both the tables but keep only one copy of each common column |
In Inner Join, The resulting table will contain all the attribute of both the tables including duplicate columns also |
In Natural Join, If there is no condition specifies then it returns the rows based on the common column |
In Inner Join, only those records will return which exists in both the tables |
Syntax- SELECT * FROM table1 NATURAL JOIN table2; |
Syntax- SELECT * FROM table1 INNER JOIN table2 ON table1.Column_Name= table2.Column_Name; |
The Natural Joins are not supported in the SQL Server Management Studio also known as Microsoft SQL Server.
Conclusion
In this post, we have mostly discussed the NATURAL JOIN and INNER JOIN as well as their distinctions. In the structured query language, both types of joins—whether they be Natural joins or Inner joins—play a significant role. An explicit join condition is not necessary for a natural join. The primary distinction is that whereas natural joins combine tables when they share a column name, inner joins combine tables that are expressly defined in the ON clause.
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...