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. |
Back