Difference between Inner Join and Outer Join in SQL

1. Inner Join :
It is a type of join operation in SQL. Inner join is an operation that returns a combined tuples between two or more tables where at least one attribute in common. If there is no attribute in common between tables then it will return nothing.

Syntax:

select * 
from table1 INNER JOIN table2
on table1.column_name = table2.column_name;

select *
from table1 JOIN table2
on table1.column_name = table2.column_name;

2. Outer Join :
It is a type of Join operation in SQL. Outer join is an operation that returns a combined tuples from a specified table even the join condition will fail. There are three types of outer join in SQL i.e.

Left Outer Join
Right Outer Join
Full Outer Join

Syntax of Left Outer Join:

select *
from table1 LEFT OUTER JOIN table2
on table1.column_name = table2.column_name;

Syntax of Right Outer Join:

select *
from table1 RIGHT OUTER JOIN table2
on table1.column_name = table2.column_name;

Syntax of Full Outer Join:

select *
from table1 FULL OUTER JOIN table2
on table1.column_name = table2.column_name;

Below is a table of difference between INNER JOIN and OUTER JOIN:

S.No	Inner Join	Outer Join
1.	It returns the combined tuple between two or more tables.	It returns the combined tuple from a specified table even join condition will fail.
2.	Used clause INNER JOIN and JOIN.	Used clause LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, etc.
3.	When any attributes are not common then it will return nothing.	It does not depend upon the common attributes. If the attribute is blank then here already placed NULL.
4.	If tuples are more. Then INNER JOIN works faster than OUTER JOIN.	Generally, The OUTER JOIN is slower than INNER JOIN. But except for some special cases.
5.	It is used when we want detailed information about any specific attribute.	It is used when we want to complete information.
6.	JOIN and INNER JOIN both clauses work the same.	FULL OUTER JOIN and FULL JOIN both clauses work the same.
7.	SQL Syntax: select * from table1 INNER JOIN / JOIN table2 ON table1.column_name = table2.column_name;	SQL Syntax: select * from table1 LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL OUTER JOIN / FULL JOIN table2 ON table1.column_name = table2.column_name;

Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.

My Personal Notes

Recommended Posts: