Difference between “INNER JOIN” and “OUTER JOIN”
JOINS :
Joins in SQL are used to combine rows from multiple tables on a specific condition, which is a relation between the columns of two tables. And there are different types of joins and in this article let us cover INNER JOIN and OUTER JOIN and their differences.
Let us consider the two tables student and location and see how the differences would look like by combing tables using different joins.
Table1 –
student_id |
student_name |
12 |
Gupta |
16 |
Girish |
17 |
Gupta |
14 |
Kunal |
15 |
Krishna |
18 |
Satish |
student
Table2 –
student_id |
student_location |
12 |
Delhi |
13 |
Madras |
15 |
Tamil Nadu |
14 |
Mumbai |
16 |
Telangana |
20 |
Punjab |
location
Firstly, creating tables and inserting data into tables using MSSQL as a server:
- Creating the student tables and location using the following queries –
Created two tables
- Inserting rows into student tables and location using the following queries –
Inserted data into tables
- Viewing the tables using the following query –
Viewing data from tables
Types of JOINS :
1. INNER JOIN
2. OUTER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
1. Inner Join :
When the inner join is used, it considers only those attributes that we want to match both the table and, if anything that doesn’t, wouldn’t be included in our result table.
Two types of Inner Join –
- Equi Join –
It is the subcategory of Inner Join where it is restricted to only equality condition in the table. The join is said to be Equi join if and only if there is an equality condition in the query.
The query for Equi Join on the above two tables:
SELECT * FROM
student
INNER JOIN
location
ON
student.student_id = location.student_id;
OUTPUT TABLE –
- Self Join –
Self Join considers the same table as another table and outputs the resultant table after the required condition satisfies.
The query for Self Join is in the above two tables:
SELECT s1.student_id ,s1.student_name FROM
student s1
INNER JOIN
student s2
ON
s1.student_name= s2.student_name AND s1.student_id<> s2.student_id;
OUTPUT TABLE –
Venn diagram representation of the Inner Join –
2. Outer Join :
In the outer join, we consider any of the tables completely or both such that the remaining fields that were unmatched in both the tables were kept NULL.
Three types of Outer Join –
1. Left Join or (left outer join) –
In left join, we consider the left table completely and the matched attributes (based on condition) in the right table along with, the unmatched attributes of the left table with the right table are placed NULL with respect to the column in the left table.
The query for Left Join is in the above two tables:
SELECT * FROM
student
LEFT JOIN
location
ON
student.student_id = location.student_id;
OUTPUT TABLE:
Venn diagram representation of the Left Join:
Right, Join –
In the right join, we consider the right table completely and the matched attributes (based on condition) in the left table along with, the unmatched attributes of the right table with the left table are placed NULL with respect to a column in the right table.
The query for Right Join is in the above two tables:
SELECT * FROM
student
RIGHT JOIN
location
ON
student.student_id = location.student_id;
OUTPUT TABLE –
Venn diagram representation of the Right Join –
Full Join –
It is the union of both left join and right join where all the columns of the left table and the right table are considered where the unmatched or unfound attributes of the left table or right table will be placed with NULL in the resultant table.
The query for Full Join is in the above two tables:
SELECT * FROM
student
FULL JOIN
location
ON
student.student_id = location.student_id;
OUTPUT TABLE –
Venn diagram representation of the full Join –
Last Updated :
10 Jun, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...