Let’s understand what exactly is “INNER JOIN” and “OUTER JOIN”, then we’ll gradually understand the differences between them:
INNER JOIN
Definition: Inner Join is also known as Natural Join. The result-set of an Inner Join is the commonality between the two or more tables. So, Inner Join always compares two or more tables and combines the matching rows or tuples in the combined tables. It’s also referred to as the default type of join because we write join clause without using any inner keyword and it performs Natural Join. So, if you write Join clause without mentioning Outer Keyword then also it’ll perform the inner join.
Have a look at this video to understand the INNER JOIN in detail.
Example:
There are two tables Table A: Employee Table and Table B: Department Table. Now let’s understand what is the result after performing Inner Join on these Table.
Employee_ID
Name
Department_ID
Salary
5110
Joseph
HR
30,000
5111
Harry
MN
45,000
5112
Scarlet
OP
27,000
5113
Peter
CC
30,000
5114
Haely
TC
35,000
5115
James
HR
33,000
5116
Roobina
MN
47,000
5117
Lily
OP
29,000
Table A: Employee
Department_ID
Department_name
HR
Human Resource
MN
Management
OP
Operation
TC
Technical
SC
Sales
Table B: Department
Query:
SELECT Name, Salary, Department_name
FROM Employee INNER JOIN Department ON Employee.Department_ID= Department.ID.
Name
Salary
Department_name
Joseph
30,000
Human Resource
Harry
45,000
Management
Scarlet
25,000
Operation
James
33,000
Human Resource
Haley
35,000
Technical
Roobina
47,000
Management
Lily
29,000
Operation
Result of Inner Join.
Outer Join
Definition: In Outer Join, only those rows are given as output that has the same attribute values in both the tables which are getting compared. The result of Outer Join is all the tuples of both the table.
Outer Join is of three following types:
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join.
Let us understand the types of Outer Join one by one.
1. Left Outer Join: This keyword returns all the records from the left table (table1) and the matched records from the right table (table2). If there is no match, then the result is NULL from the right side in the resultant table.
Head over to this video to understand LEFT OUTER JOIN in detail.
Let’s consider the above Employees and Department Table for example to understand the resultset of the LEFT OUTER JOIN:
Query:
Select Name, Department_name From Employee Left Outer Join Department ON Employee.Department_ID=Depoartment.ID.
Left Outer Join’s Result: All the tuples from Employee Table are displayed in the result.
Name
Department_name
Joseph
Human Resource
Harry
Management
Scarlet
Operation
Peter
Null
Haely
Technical
James
Human Resource
Roobina
Management
Lily
Operation
2. Right Outer Join: This keyword returns all records from the right table (table2), and the matched records from the left table (table1). If there is no match, the result is NULL from the left side.
For more information you can refer to this video:
Let’s consider the above Employees and Department Table for example to understand the resultset of RIGHT OUTER JOIN:
Query:
Select Name, Department_name From Department Right Outer Join Student ON Employee.Department_ID=Depoartment.ID.
Right Outer Join’s Result: All the rows from the Department table are displayed.
Name
Department_name
Joseph
Human Resource
Harry
Management
Scarlet
Operation
Haely
Technical
James
Human Resource
Roobina
Management
Lily
Operation
Null
Sales
3. Full Outer Join: This keyword returns all the records when there is a match in either left (table1) or right (table2) table rows (records).
You can also refer to this video for detailed information about Full Outer Join:
Let’s consider the above Employees and Department Table for example to understand the resultset of the FULL OUTER JOIN:
Query:
Select Name, Department_name From Employee Full Outer Join Department ON Employee.Department_ID=Depoartment.ID.
Full Outer Join’s Result: All the tuples from both the tables are included in the result.
Name
Department_name
Joseph
Human Resource
Harry
Management
Scarlet
Operation
Peter
Null
Haely
Technical
James
Human Resource
Roobina
Management
Lily
Operation
Null
Sales
Now, let’s compare the Inner Join and Outer Join Based on some parameters:
Comparison
Inner Join
Outer Join
DB(Database)
The size of the database returned by Inner Join is smaller than the Outer Join
This returns a comparatively larger database.
Types
No Type
Left Outer Join, Right Outer Join and Full Outer Join
Fundamental
Inner Join returns only the matching rows from both the table.
This returns all the rows from both the tables.