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