What is Inner join and natural join?
Posted By: Vaishnavi Mall Published: 14, Jan 2024
Inner Join Vs Natural Join
SQL
SQL stands for Structured Query Language. It is a computer language to create, manage and manipulate databases. SQL is used in relational database systems to create, retrieve, update and delete tables in databases. There are many different types of statements, clauses, and operators in SQL. In this blog, we are going to see how INNER JOIN is different from NATURAL JOIN in SQL.
JOIN
In SQL, JOIN clauses are used to retrieve data by combining two or more tables on the basis of a column shared between them.
There are four types of JOIN in SQL.
- INNER JOIN
- OUTER JOIN
- LEFT JOIN
- RIGHT JOIN
KEY DIFFERENCES BETWEEN INNER JOIN AND NATURAL JOIN
| INNER JOIN | NATURAL JOIN |
|---|---|
| Inner join is used to join two tables based on the column name specified explicitly in the ON clause. | Natural join is used to join two tables based on the column that has the same name and datatype and is present in both the tables. |
| The resulting table created using Inner join includes all the columns formed including the duplicate column that is present in both the tables. For example, the dept_id column is shown two times in the resulting table as shown above. | The resulting table created using Natural join includes all the columns formed but only one copy of the duplicate column that is present in both the tables. For example, the dept_id column is shown only times in the resulting table as shown above. |
Let's take an example database consisting of two tables :
Employee Table
| emp_Id | emp_name | emp_age | dept_id |
|---|---|---|---|
| 1 | Vaishnavi | 19 | 2 |
| 2 | Rimjhim | 19 | 1 |
| 3 | Riya | 19 | 1 |
| 4 | Kanika | 19 | 4 |
Department Table
| id | dept_name | dept_pay | dept_code |
|---|---|---|---|
| 1 | Frontend | 10000 | D01 |
| 2 | Backend | 12000 | D02 |
| 3 | QA | 13000 | D03 |
| 4 | Content Writer | 9000 | D05 |
INNER JOIN
The INNER JOIN is used to combine two tables based on the condition specified
QUERY :
Select * FROM `employee` e
INNER JOIN `department` d
ON (e.dept_id = d.dept_id)
NATURAL JOIN
The NATURAL JOIN is used to combine two tables based on a column with the same name and datatype present on both the tables.
QUERY :
Select * FROM `employee` e
NATURAL JOIN `department` d