Difference between Natural join and Inner Join in SQL
Both Natural Join and Inner Join are used to combine data from two or more tables in SQL. However, there are some differences between them:
- Natural Join:
- Natural Join is a type of Join that combines two tables based on the columns with the same name and data type.
- It automatically matches the columns with the same name and data type in both tables and returns the result set.
- It is a type of Inner Join, but it does not require the use of the "ON" keyword to specify the join condition.
- Natural Join can be used when the tables have the same column names and data types, and the join condition is based on those columns.
Example:
Table1: Employees
| EmpID | Name | Dept |
|---|---|---|
| 1 | John | IT |
| 2 | Mary | HR |
| 3 | Bob | IT |
Table2: Departments
| Dept | Location |
|---|---|
| IT | NY |
| HR | LA |
Query:
SELECT * FROM Employees NATURAL JOIN Departments;
Result:
| EmpID | Name | Dept | Location |
|---|---|---|---|
| 1 | John | IT | NY |
| 2 | Mary | HR | LA |
| 3 | Bob | IT | NY |
- Inner Join:
- Inner Join is a type of Join that returns only the matching rows from both tables based on a specified join condition.
- It requires the use of the "ON" keyword to specify the join condition.
- Inner Join can be used when the tables have different column names or data types, and the join condition is based on those columns.
Example:
Table1: Employees
| EmpID | Name | Dept |
|---|---|---|
| 1 | John | IT |
| 2 | Mary | HR |
| 3 | Bob | IT |
Table2: Departments
| DeptID | DeptName | Location |
|---|---|---|
| 1 | IT | NY |
| 2 | HR | LA |
Query:
SELECT * FROM Employees INNER JOIN Departments ON Employees.Dept = Departments.DeptName;
Result:
| EmpID | Name | Dept | DeptID | DeptName | Location |
|---|---|---|---|---|---|
| 1 | John | IT | 1 | IT | NY |
| 2 | Mary | HR | 2 | HR | LA |
| 3 | Bob | IT | 1 | IT | NY |
In summary, Natural Join automatically matches the columns with the same name and data type in both tables, while Inner Join requires the use of the "ON" keyword to specify the join condition. Natural Join can only be used when the tables have the same column names and data types, while Inner Join can be used when the tables have different column names or data types.
About the author
William Pham is the Admin and primary author of Howto-Code.com. With over 10 years of experience in programming. William Pham is fluent in several programming languages, including Python, PHP, JavaScript, Java, C++.