Open In App

Difference between Inner Join and Outer Join in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

1. Inner Join : 
It is a type of join operation in SQL. Inner join is an operation that returns combined tuples between two or more tables where at least one attribute is 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;

OR 
 

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 combined tuples from a specified table even if the join condition fails. There are three types of outer join in SQL i.e. 

  1. Left Outer Join
  2. Right Outer Join
  3. 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;

Difference between INNER JOIN and OUTER JOIN

Below is a table of differences 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 if the join condition fails.
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 there is 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; 
 

Last Updated : 24 Dec, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads