SQL JOIN
As the name says, joins mean to merge something or to combine something. But in the case of SQL, joins mean to merge or combine two different tables.
The Join clause uses in two tables, which are the parent and child tables. Join clause takes records from one or more tables and combines the records.
There are different types of joins used in SQL are as follows:
1. Inner Join
2. Left Outer Join
3. Right Outer Join
4. Full Outer Join
SQL Joins takes records from two different tables but displays results in a single table.
Let's understand each type of join one by one with the example.
1. Inner Join
Inner Join in SQL is a widely used join. It takes all the records from both tables until and unless the conditions match. It means this join will return only those common rows in both tables.
The syntax of the inner join is as follows:
SELECT Table_Name_1.Column_Name_1, Table_Name_1.Column_Name_2, Table_Name_2.Column_Name_1, Table_Name_2.Column_Name_2 FROM Table_Name_1 INNER JOIN Table_Name_2 ON Table_Name_1.Column_Name = Table_Name_2.Column_Name;
Let’s understand inner join more by looking at an example:
We have two tables:
Table Name 1: Employee_Details table with certain columns
E_id | E_Name | E_salary | E_City |
1001 | Pranoti Shende | 60000 | Pune |
1002 | Vaibhav Sharma | 58000 | Mumbai |
1003 | Nikhil Vani | 50000 | Mumbai |
1004 | Prachi Sharma | 60000 | Hyderabad |
1005 | Harshada Koli | 48500 | Nashik |
1006 | Sonal Maheshwari | 50000 | Bangalore |
1007 | Bhavesh Jain | 65000 | Pune |
1008 | Kapil Verma | 50000 | Nashik |
1009 | Rajesh Goud | 55000 | Hyderabad |
1010 | Deepam Jauhari | 60000 | Bangalore |
Table Name 2: Comp
Table with certain columns (E_Id Common in both tables)
Comp_Id | Make | E_Id |
101 | Dell | 1001 |
102 | Dell | 1004 |
103 | Lenovo | 1006 |
104 | Lenovo | 1008 |
105 | HP | 1010 |
106 | HP | 1005 |
107 | Asus | 1003 |
We want to display employee Id, names, and make from the comp table.
To produce this as output, execute this query as follow:
SELECT ED.E_Id, ED.E_Name, Make FROM Employee_Details ED INNER JOIN Comp C ON ED.E_Id = C.E_Id;
The output of the above query is as follows:
E_id | E_Name | Make |
1001 | Pranoti Shende | Dell |
1004 | Prachi Sharma | Dell |
1006 | Sonal Maheshwari | Lenovo |
1008 | Kapil Verma | Lenovo |
1010 | Deepam Jauhari | HP |
1005 | Harshada Koli | HP |
1003 | Nikhil Vani | Asus |

Suppose, while using inner joins, there can be a situation where you want to filter rows based on some criteria. In such a case, we can use the where clause.
Let's take an example and understand the example of how where clause works in joins:
SELECT ED.E_Id, ED.E_Name, ED.E_City, Make FROM Employee_Details ED INNER JOIN Comp C ON ED.E_Id = C.E_Id WHERE E_City = ‘Bangalore’;
We display the employee's information whose employee resides in Bangalore City in the above query.
The output of the above query is as follows:
E_id | E_Name | E_City | Make |
1006 | Sonal Maheshwari | Bangalore | Lenovo |
1010 | Deepam Jauhari | Bangalore | HP |

For example, you want to display employee id, name, city and salary, comp id and make where group by comp Id; then we will execute the below query as follow:
SELECT ED.E_Id, ED.E_Name, ED.E_Salary, ED.E_City, C.Comp_Id, Make FROM Employee_Details ED INNER JOIN Comp C ON ED.E_Id = C.E_Id GROUP BY Comp_Id;
The output of the above query is as follows:
E_id | E_Name | E_salary | E_City | Comp_Id | Make |
1001 | Pranoti Shende | 60000 | Pune | 101 | Dell |
1004 | Prachi Sharma | 60000 | Hyderabad | 102 | Dell |
1006 | Sonal Maheshwari | 50000 | Bangalore | 103 | Lenovo |
1008 | Kapil Verma | 50000 | Nashik | 104 | Lenovo |
1010 | Deepam Jauhari | 60000 | Bangalore | 105 | HP |
1005 | Harshada Koli | 48500 | Nashik | 106 | HP |
1003 | Nikhil Vani | 50000 | Mumbai | 107 | Asus |

2. Left Outer Join
The left outer join returns all the records from (left table) table one, whether the record in table 2 matches or not, according to the join condition. The record, which matches their result set is the same as the inner join result, and all uncommon records from another table will result in null.
Let’s understand Left Outer join more by looking at an example:
We have two tables:
Table Name 1: Employee_Details table with certain columns
E_id | E_Name | E_salary | E_City |
1001 | Pranoti Shende | 60000 | Pune |
1002 | Vaibhav Sharma | 58000 | Mumbai |
1003 | Nikhil Vani | 50000 | Mumbai |
1004 | Prachi Sharma | 60000 | Hyderabad |
1005 | Harshada Koli | 48500 | Nashik |
1006 | Sonal Maheshwari | 50000 | Bangalore |
1007 | Bhavesh Jain | 65000 | Pune |
1008 | Kapil Verma | 50000 | Nashik |
1009 | Rajesh Goud | 55000 | Hyderabad |
1010 | Deepam Jauhari | 60000 | Bangalore |
Table Name 2: Comp
Table with certain columns (E_Id Common in both tables)
Comp_Id | Make | E_Id |
101 | Dell | 1001 |
102 | Dell | 1004 |
103 | Lenovo | 1006 |
104 | Lenovo | 1008 |
105 | HP | 1010 |
106 | HP | 1005 |
107 | Asus | 1003 |
We want to display the employee’s name, id from the employee's table and make a name, and comp id from the comp table
To produce this as output, execute this query as follow:
SELECT ED.E_Id, ED.E_Name, C.Comp_Id, Make FROM Employee_Details ED LEFT JOIN Comp C ON ED.E_Id = C.E_Id;
The output of the above query is as follows:
E_id | E_Name | Comp_Id | Make |
1001 | Pranoti Shende | 101 | Dell |
1002 | Vaibhav Sharma | NULL | NULL |
1003 | Nikhil Vani | 107 | Asus |
1004 | Prachi Sharma | 102 | Dell |
1005 | Harshada Koli | 106 | HP |
1006 | Sonal Maheshwari | 103 | Lenovo |
1007 | Bhavesh Jain | NULL | NULL |
1008 | Kapil Verma | 104 | Lenovo |
1009 | Rajesh Goud | NULL | NULL |
1010 | Deepam Jauhari | 105 | HP |

Suppose, while using left outer joins, there can be a situation where you want to filter rows based on some criteria. In such cases, we can use the where clause.
Let's take an example and understand the example of how where clause works in joins:
SELECT ED.E_Id, ED.E_Name, ED.E_Salary, C.Comp_Id, Make FROM Employee_Details ED LEFT JOIN Comp C ON ED.E_Id = C.E_Id WHERE E_Salary BETWEEN 50000 AND 60000;
In the above query, we display the employee's information whose employee salary is between 50000 and 60000.
The output of the above query is as follows:
E_id | E_Name | E_salary | Comp_Id | Make |
1001 | Pranoti Shende | 60000 | 101 | Dell |
1002 | Vaibhav Sharma | 58000 | NULL | NULL |
1003 | Nikhil Vani | 50000 | 107 | Asus |
1004 | Prachi Sharma | 60000 | 102 | Dell |
1006 | Sonal Maheshwari | 50000 | 103 | Lenovo |
1008 | Kapil Verma | 50000 | 104 | Lenovo |
1009 | Rajesh Goud | 55000 | NULL | NULL |
1010 | Deepam Jauhari | 60000 | 105 | HP |

3. Right Outer Join
In Right Outer, join for both tables will return all the records from table 2, whether the record in table 1 matches or not to the join condition. The record which matches their result set is the same as the inner join result, and all nonmatching records from another table will result in null.
Let’s understand Right Outer join more by looking at an example:
We have two tables:
Table Name 1: Employee_Details table with certain columns
E_id | E_Name | E_salary | E_City |
1001 | Pranoti Shende | 60000 | Pune |
1002 | Vaibhav Sharma | 58000 | Mumbai |
1003 | Nikhil Vani | 50000 | Mumbai |
1004 | Prachi Sharma | 60000 | Hyderabad |
1005 | Harshada Koli | 48500 | Nashik |
1006 | Sonal Maheshwari | 50000 | Bangalore |
1007 | Bhavesh Jain | 65000 | Pune |
1008 | Kapil Verma | 50000 | Nashik |
1009 | Rajesh Goud | 55000 | Hyderabad |
1010 | Deepam Jauhari | 60000 | Bangalore |
Table Name 2: Comp
Table with certain columns (E_Id Common in both tables)
Comp_Id | Make | E_Id |
101 | Dell | 1001 |
102 | Dell | 1004 |
103 | Lenovo | 1006 |
104 | Lenovo | 1008 |
105 | HP | 1010 |
106 | HP | 1005 |
107 | Asus | 1003 |
We want to display the employee’s name, id from the employee's table and make a name, and comp id from the comp table
To produce this as output, execute this query as follow:
SELECT C.Comp_Id, Make, ED.E_Id, ED.E_Name FROM Comp C RIGHT OUTER JOIN Employee_Details ED ON ED.E_Id = C.E_Id;
The output of the above query is as follows:
Comp_Id | Make | E_id | E_Name |
101 | Dell | 1001 | Pranoti Shende |
NULL | NULL | 1002 | Vaibhav Sharma |
107 | Asus | 1003 | Nikhil Vani |
102 | Dell | 1004 | Prachi Sharma |
106 | HP | 1005 | Harshada Koli |
103 | Lenovo | 1006 | Sonal Maheshwari |
NULL | NULL | 1007 | Bhavesh Jain |
104 | Lenovo | 1008 | Kapil Verma |
NULL | NULL | 1009 | Rajesh Goud |
105 | HP | 1010 | Deepam Jauhari |

Suppose, while using right outer joins, there can be a situation where you want to filter rows based on some criteria. In such cases, we can use the where clause.
Let's take an example and understand the example of how where clause works in joins:
SELECT C.Comp_Id, Make, ED.E_Id, ED.E_Name, ED.E_Salary FROM Comp C RIGHT OUTER JOIN Employee_Details ED ON ED.E_Id = C.E_Id WHERE E_Salary BETWEEN 50000 AND 60000;
In the above query, we display the employee's information whose employee salary is between 50000 and 60000.
The output of the above query is as follows:
Comp_Id | Make | E_id | E_Name | E_salary |
101 | Dell | 1001 | Pranoti Shende | 60000 |
NULL | NULL | 1002 | Vaibhav Sharma | 58000 |
107 | Asus | 1003 | Nikhil Vani | 50000 |
102 | Dell | 1004 | Prachi Sharma | 60000 |
103 | Lenovo | 1006 | Sonal Maheshwari | 50000 |
104 | Lenovo | 1008 | Kapil Verma | 50000 |
NULL | NULL | 1009 | Rajesh Goud | 55000 |
105 | HP | 1010 | Deepam Jauhari | 60000 |

4. Full Outer Join
Full Outer Join merges the result of both outer joins, Left Outer Joins and the Right Outer Join. Full Outer Joins returns the output, which matches rows and unmatched rows between the two tables. Full Outer Join is the same as Cross Join.
Let’s understand Full Outer join more by looking at an example:
We have two tables:
Table Name 1: Employee_Details table with certain columns
E_id | E_Name | E_salary | E_City |
1001 | Pranoti Shende | 60000 | Pune |
1002 | Vaibhav Sharma | 58000 | Mumbai |
1003 | Nikhil Vani | 50000 | Mumbai |
1004 | Prachi Sharma | 60000 | Hyderabad |
1005 | Harshada Koli | 48500 | Nashik |
1006 | Sonal Maheshwari | 50000 | Bangalore |
1007 | Bhavesh Jain | 65000 | Pune |
1008 | Kapil Verma | 50000 | Nashik |
1009 | Rajesh Goud | 55000 | Hyderabad |
1010 | Deepam Jauhari | 60000 | Bangalore |
Table Name 2: Comp
Table with certain columns (E_Id Common in both tables)
Comp_Id | Make | E_Id |
101 | Dell | 1001 |
102 | Dell | 1004 |
103 | Lenovo | 1006 |
104 | Lenovo | 1008 |
105 | HP | 1010 |
106 | HP | 1005 |
107 | Asus | 1003 |
We want to display all the details from the employee_details and comp tables where salary is between 55000 and 60000. We will use the below query:
SELECT * FROM Employee_Details FULL JOIN Comp WHERE E_Salary BETWEEN 55000 AND 60000;
The output of the above query is as follows:
E_id | E_Name | E_salary | E_City | Comp_Id | Make | E_Id |
1001 | Pranoti Shende | 60000 | Pune | 101 | Dell | 1001 |
1001 | Pranoti Shende | 60000 | Pune | 102 | Dell | 1004 |
1001 | Pranoti Shende | 60000 | Pune | 103 | Lenovo | 1006 |
1001 | Pranoti Shende | 60000 | Pune | 104 | Lenovo | 1008 |
1001 | Pranoti Shende | 60000 | Pune | 105 | HP | 1010 |
1001 | Pranoti Shende | 60000 | Pune | 106 | HP | 1005 |
1001 | Pranoti Shende | 60000 | Pune | 107 | Asus | 1003 |
1002 | Vaibhav Sharma | 58000 | Mumbai | 101 | Dell | 1001 |
1002 | Vaibhav Sharma | 58000 | Mumbai | 102 | Dell | 1004 |
1002 | Vaibhav Sharma | 58000 | Mumbai | 103 | Lenovo | 1006 |
1002 | Vaibhav Sharma | 58000 | Mumbai | 104 | Lenovo | 1008 |
1002 | Vaibhav Sharma | 58000 | Mumbai | 105 | HP | 1010 |
1002 | Vaibhav Sharma | 58000 | Mumbai | 106 | HP | 1005 |
1002 | Vaibhav Sharma | 58000 | Mumbai | 107 | Asus | 1003 |
1004 | Prachi Sharma | 60000 | Hyderabad | 101 | Dell | 1001 |
1004 | Prachi Sharma | 60000 | Hyderabad | 102 | Dell | 1004 |
1004 | Prachi Sharma | 60000 | Hyderabad | 103 | Lenovo | 1006 |
1004 | Prachi Sharma | 60000 | Hyderabad | 104 | Lenovo | 1008 |
1004 | Prachi Sharma | 60000 | Hyderabad | 105 | HP | 1010 |
1004 | Prachi Sharma | 60000 | Hyderabad | 106 | HP | 1005 |
1004 | Prachi Sharma | 60000 | Hyderabad | 107 | Asus | 1003 |
1009 | Rajesh Goud | 55000 | Hyderabad | 101 | Dell | 1001 |
1009 | Rajesh Goud | 55000 | Hyderabad | 102 | Dell | 1004 |
1009 | Rajesh Goud | 55000 | Hyderabad | 103 | Lenovo | 1006 |
1009 | Rajesh Goud | 55000 | Hyderabad | 104 | Lenovo | 1008 |
1009 | Rajesh Goud | 55000 | Hyderabad | 105 | HP | 1010 |
1009 | Rajesh Goud | 55000 | Hyderabad | 106 | HP | 1005 |
1009 | Rajesh Goud | 55000 | Hyderabad | 107 | Asus | 1003 |
1010 | Deepam Jauhari | 60000 | Bangalore | 101 | Dell | 1001 |
1010 | Deepam Jauhari | 60000 | Bangalore | 102 | Dell | 1004 |
1010 | Deepam Jauhari | 60000 | Bangalore | 103 | Lenovo | 1006 |
1010 | Deepam Jauhari | 60000 | Bangalore | 104 | Lenovo | 1008 |
1010 | Deepam Jauhari | 60000 | Bangalore | 105 | HP | 1010 |
1010 | Deepam Jauhari | 60000 | Bangalore | 106 | HP | 1005 |
1010 | Deepam Jauhari | 60000 | Bangalore | 107 | Asus | 1003 |
