Inner Join and Outer Join both are the types of Join. Join compares and combines tuples from two relations or tables. Inner Join specifies the natural join i.e. if you write a Join clause without Inner keyword then it performs the natural join operation. The potential difference between Inner Join and Outer Join is that Inner Join returns only the matching tuples from both the table and the Outer Join returns all the tuples from both the compared tables. Let us discuss some other differences between Inner Join and Outer Join with the help of the comparison chart shown below.
Content: Inner Join Vs Outer Join
|Basis for Comparison||Inner Join||Outer Join|
|Basic||Inner Join outputs only the matching tuples from both the table.||Outer Join displays all the tuples from both the tables.|
|Database||The Potential size of the database returned by Inner Join is comparatively smaller than Outer Join.||Outer join return comparatively larger database.|
|Types||No types.||Left Outer Join,
Right Outer Join,
and Full Outer Join.
Definition of Inner Join
Inner Join is also referred to as Natural Join. Inner Join compares two tables and combines the matching tuple in both the tables. It is also called as the default type of join, as Join clause is written without the inner keyword it perform the natural join. If the Join clause is written without Outer keyword then also inner join is performed.
Inner Join can be explained with an example. There are two tables student table and department table. Now lets us understand what does inner Join performs.
SELECT Name, Sem, Deparment_name FROM Student INNER JOIN Department ON Student.Department_ID= Department.ID.
You can see that only those tuples are obtained in the resultant where Student.Department_ID= Department.ID. Hence, we can say that Inner Join combines only the matching tuple of two table.
Definition of Outer Join
Unlike in Inner Join, only those tuples are output that has same attribute values in both the compared table; Outer Join outputs all the tuples of both the table. Outer Join is of three types Left Outer Join, Right Outer Join, and Full Outer Join.
Let us understand them one by one. First, let us take Left Outer Join.
Select Name, Department_name From Student Left Outer Join Department ON Student.Department_ID=Depoartment.ID.
You can see that all the tuples from Student Table are displayed in the result.
Select Name, Department_name From Department Right Outer Join Student ON Student.Department_ID=Depoartment.ID.
You can see that all the tuples from Department table are displayed.
Select Name, Department_name From Student Full Outer Join Department ON Student.Department_ID=Depoartment.ID.
You can observe that all the tuples from both the tables are displayed in the result.
Key Differences Between Inner Join and Outer Join
- The basic difference between the Inner Join and Outer Join is that inner join compares and combine only the matching tuples from bothe the tables. On the other hands, the Outer Join compare and combines all the tuples from both the tables being compared.
- The database size of the resultant obtained from the Inner Join is smaller that Outer Join.
- There are three types of the Outer Join Left Outer Join, Righ Outer Join, and Full Outer Join. But inner Join has no such types.
Both the Joins are very useful. Ther use depends on the requirement of the user.