SQL Joins combine two or more tables together based on the corresponding common column among them.
Note: All the below mentioned queries are executed with reference to MySQL Database.
Types of SQL Joins
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- SELF JOIN
1. INNER JOIN
SQL INNER JOIN basically serves as the intersection of two or more tables. It returns elements that are common to both of the tables.

Let’s use the following tables to serve and understand the purpose of SQL Joins.
Table name: Information
Employee Id | Manager Id | Batch |
---|---|---|
1 | 1 | A |
2 | 2 | B |
3 | 5 | A |
Table name: Manager_Details
Manager Id | Name | City | Age |
---|---|---|---|
1 | Safa Mulani | Pune | 52 |
2 | Aman Mulani | Satara | 25 |
3 | Divya Trivedi | Mumbai | 24 |
Syntax:
SELECT Column_Names
FROM Table1
INNER JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;
Example:
SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
INNER JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;
Output:
1 1 Safa Mulani
2 2 Aman Mulani
2. LEFT OUTER JOIN
SQL LEFT OUTER JOIN returns all the elements from the left table(i.e. Table1) and the corresponding matched elements from the right table(i.e. Table2).
If the appropriate match isn’t found, it returns NULL values for the unmatched right table column values.

Syntax:
SELECT Column_Names
FROM Table1
LEFT JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;
Example:
SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
LEFT JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;
Output:
1 1 Safa Mulani
2 2 Aman Mulani
3 NULL NULL
3. RIGHT OUTER JOIN
SQL RIGHT OUTER JOIN returns all the elements from the right table(i.e. Table2) and the corresponding matched elements from the left table(i.e. Table1).
If the appropriate match isn’t found, it returns NULL values for the unmatched left table column values.

Syntax:
SELECT Column_Names
FROM Table1
RIGHT JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;
Example:
SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
RIGHT JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;
Output:
1 1 Safa Mulani
2 2 Aman Mulani
NULL 3 Divya Trivedi
4. FULL OUTER JOIN
SQL FULL JOIN basically represents all the elements which either matches the elements from the left table or the right table.
If the rows of either of the tables do not match each other, still the elements will be represented with NULL values attached to it.

Syntax:
SELECT Column_Names
FROM Table1
FULL OUTER JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;
Most of the Databases such as SQLite, MySQL, etc do not support FULL OUTER Joins.
In order to serve the purpose, we need to modify the query and make use of UNION clause to provide the functionalities of the FULL OUTER JOIN.
Syntax:
SELECT Column_Names
FROM Table1
LEFT OUTER JOIN Table2
ON Table1.Column_Name = Table2.Column_Name
UNION
SELECT Column_Names
FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.Column_Name = Table2.Column_Name;
Example:
SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
LEFT OUTER JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id
UNION
SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.Name
FROM Information
RIGHT OUTER JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;
Output:
1 1 Safa Mulani
2 2 Aman Mulani
3 NULL NULL
NULL 3 Divya Trivedi
5. SELF JOIN
SQL SELF JOIN represents a join to itself. i.e it renames the table, gives alias names to the corresponding table.
Syntax:
SELECT Column_Name
FROM Table1 A, Table1 B
WHERE condition;
Here, A and B are the alias names given to the same Table1.
Example:
SELECT a.Manager_Id, b.Name, a.City
FROM Manager_details a, Manager_details b
WHERE a.Age > 24 AND b.Age > 24;
Output:
1 Safa Mulani Pune
2 Safa Mulani Satara
1 Aman Mulani Pune
2 Aman Mulani Satara
Conclusion
Thus, in this article, we have understood the functionalities of SQL Joins.