Joins in SQL are used to return rows by combining and comparing different tables present in a database. Joins are classified into two types:
- Inner Join
- Outer Join
Inner Join
Inner Join is used to find the similarities between two tables present in a database. Inner Join searches for overlapping data in a table. If overlapping data is present, the inner join will combine the overlapping data and display it in a new table.
Outer Join
Outer Join is used to find dissimilarities between two tables present in a database. Outer Join will find the dissimilarities between the tables and display it in a new table. There are four types outer join they are:
- Left Outer Join
- Full Outer Join
- Right Outer Join
Example for Inner Join
Inner Join concentrate on the similarities between two tables present in a database. Inner join will return a new table if there is any matching data between the two tables. Let us consider a scenario in database where there are two tables. Table 1 contains student name and subjects while table 2 contains subjects name and marks. If there is any similarities between subject names present in the two tables, it can be displayed using inner join.
Table:1
| Subject Name | Student Name |
| Physics | Smith |
| Geography | Allen |
| History | Alfred |
| Chemistry | Wayne |
| Literature | Robin |
Table: 2
| Subject Name | Marks |
| Physics | 75 |
| Zoology | 80 |
| Chemistry | 94 |
| Literature | 78 |
| Botany | 64 |
| Geography | 82 |
| History | 78 |
If table 1 and table 2 are combined using inner join, the resultant table will resemble like the table given below:
| Subject Name | Marks | Student Name |
| Physics | 75 | Smith |
| Chemistry | 94 | Wayne |
| Literature | 78 | Robin |
| Geography | 82 | Allen |
| History | 78 | Alfred |
Example for Outer Join
The resultant table which used outer join will contains the result of inner join and table content to which no corresponding match is found.
Left Outer Join
Left outer join will display all the left side entries present in the tables. In our table 2 there are two subjects namely Botany and Zoology don’t have any corresponding entry table 1. If left outer join is used to to search between these tables, the subjects which don’t have any corresponding entries will returned as “Null”.
The resultant table for left outer join is:
| Subject Name | Marks | Student Name |
| Physics | 75 | Smith |
| Chemistry | 94 | Wayne |
| Literature | 78 | Robin |
| Zoology | 80 | NULL |
| Botany | 64 | NULL |
| Geography | 82 | Allen |
| History | 78 | Alfred |
Right Outer Join
Similar to left outer join, Right outer join will display all entries on right side and if there is no corresponding entries it will returned as null.
| Marks | Student Name | Subject Name |
| 75 | Smith | Physics |
| 80 | NULL | Zoology |
| 94 | Wayne | Chemistry |
| 78 | Robin | Literature |
| 64 | NULL | Botany |
| 82 | Allen | Geography |
| 78 | Alfred | History |
- Read: More Interview Questions
Full Outer Join
Full outer join duplicates all the contents from two tables and return it in a single table. The contents missing are returned as null. The popular “MySQL” database does not use full outer join. The resultant table for full outer join is given below.
| Subject Name | Student Name | Subject Name | Marks |
| Physics | Smith | Physics | 75 |
| NULL | NULL | Zoology | 80 |
| Chemistry | Wayne | Chemistry | 94 |
| Literature | Robin | Literature | 78 |
| NULL | NULL | Botany | 64 |
| Geography | Allen | Geography | 82 |
| History | Alfred | History | 78 |
Difference between Inner and Outer Join
- Inner Join only return rows when there is a match between tables. Outer join returns the match and datas which don’t have match are returned as NULL.
- In outer join columns from both the tables are used while inner join uses rows which have matching data.
- Keywords are not required to use inner join. Outer join uses keywords to utilize various types of outer joins.
- Only rows with matching data are used in inner joins. Outer joins will utilize all the rows from both the tables.
- Inner Join will return one record for every matched data between the tables. Outer Join will return a record for every matched data and a record will returned for every “NULL” data.
Both Inner and Outer joins are obsolete terms in SQL. However, it is important to learn the concepts of inner and outer joins to understand higher concepts in SAP and ABAP.