Difference Between Inner Join and Outer Join in SQL

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

1. Comparison Chart
2. Definition
3. Key Differences
4. Conclusion

Comparison Chart

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.

Conclusion

Both the Joins are very useful. Ther use depends on the requirement of the user.