Types of SQL JOIN

The SQL JOIN combines one or more than one tables based on their relationship. The SQL JOIN involves a parent table and a child table relationship.

There are different types of SQL JOINS:

1. Inner Join
2. Left Outer Join
3. Right Outer Join
4. Full Outer Join
5. Cross Join.

Let's understand each type of SQL joins with the help of examples.

Inner Join

Inner Join in SQL is a widely used join. It fetched all the records from both tables until and unless the condition matched. It means this join will return only those rows common in both tables.

Syntax of SQL Inner Join:

SELECT  Table_Name1.Colum_Name1,     Table_Name1.Column_Name2, Table_Name1.Column_Name3,Table_Name2.Column_Name1,Table_Name2.Column_Name2, Table_Name2.Column_Name3, FROM Table_Name1 INNER JOIN Table_Name2 ON table_Name1.Column_Name = Table_Name2.Column_Name;

Consider the already existing tables, which have the certain data:

Table 1: Employees

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

Table 2: Manager

Managerid	manager_name	manager_department
1	Snehdeep Kaur	ORACLE
2	Kirti Kirtane	FMW
3	Abhishek Manish	JAVA
4	Anupam Mishra	TESTING
5	Akash Kadam	C#

Table 3: Laptop:

LAPTOPID	NAME	EMPLOYEEID
L101	DELL	NULL
L102	HP	1002
L103	LENOVO	NULL
L104	HP	3003
L105	DELL	4002
L106	LENOVO	4003
L107	DELL	5001
L108	HP	NULL
L109	DELL	NULL
L110	HP	NULL

Examples of SQL Inner Join

Example 1: Execute a query to join Employee Table and Manager Table and display employees’ details like employee id, employee name, salary from the employee's table, and manager id and manager name from the manager table.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, M.MANAGERID, M.MANAGER_NAME FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID =M.MANAGERID;

In the above query, we fetched employee id, first name, last name, salary from the employee's table and manager id, manager name from manager table where employees’ table manager id column equals to manager table manager id column. The query will return all the matches records from both tables. Manager id is a common column between both tables. E is an alias name for the employee's table, while M is for the manager table. The manager id field is placed as a foreign key on the employee table. The manager id works as the primary key in the manager table, which creates a parent-child relationship between the two tables.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	MANAGERID	MANAGER_NAME
1001	VAIBHAVI	MISHRA	65500	1	Sneedeep Kaur
2001	PRACHI	SHARMA	55500	1	Sneedeep Kaur
4003	RUCHIKA	AGARWAL	60000	1	Sneedeep Kaur
1003	NIKHIL	VANI	50500	2	Kirti kirtane
2002	BHAVESH	JAIN	65500	2	Kirti kirtane
3002	ANUJA	WANRE	50500	2	Kirti kirtane
3001	PRANOTI	SHENDE	55500	3	Abhishek Manish
3003	DEEPAM	JAUHARI	58500	3	Abhishek Manish
4002	ASHWINI	BAGHAT	54500	3	Abhishek Manish
4001	RAJESH	GOUD	60500	4	Anupam Mishra
5001	ARCHIT	SHARMA	55500	4	Anupam Mishra
1002	VAIBHAV	SHARMA	60000	5	Akash Kadam
2003	RUCHIKA	JAIN	50000	4	Akash Kadam

The records are displayed in ascending order by manager id.

Example 2: Execute a query to join Employee Table and Manager table, and display employee detail’s like employee id, employee name, salary from the employee's table and manager id and manager department from the manager table where employee salary is greater than 58000.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, M.MANAGERID, M.MANAGER_DEPARTMENT FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID =M.MANAGERID WHERE SALARY > 58000;

In the above query, we fetched the employee id, first name, last name, salary from the employee's table, manager id, and manager department from the manager table where the manager id from the employees' table is equal to the manager id from manager table only those employee records whose salary is greater than 58000.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	MANAGERID	MANAGER_DEPARTMENT
1001	VAIBHAVI	MISHRA	65500	1	ORACLE
4003	RUCHIKA	AGARWAL	60000	1	ORACLE
2002	BHAVESH	JAIN	65500	2	FMW
3003	DEEPAM	JAUHARI	58500	3	JAVA
4001	RAJESH	GOUD	60500	4	TESTING
1002	VAIBHAV	SHARMA	60000	5	C#

Only six records are displayed where an employee's salary exceeds 58000.

Example 3: Execute a query to join the Employees' table and Laptop Table, display employee details like employee id, employee name, and salary, the city from the employee's table, laptop id, and laptop name from the laptop table

SELECT EMP.EMPLOYEEID, EMP.FIRST_NAME, EMP.SALARY, EMP.CITY, LAP.LAPTOPID, LAP. NAME FROM EMPLOYEES EMP INNER JOIN LAPTOP LAP ON EMP.EMPLOYEEID = LAP.EMPLOYEEID;

In the above query, we fetched the employee id, first name, salary, the city from the employees’ table laptop id, and name from laptop table where employee id from employee table equals employee id from laptop table. Employee id is a foreign key in the Laptop table, which creates a parent-child relationship between the Employee table and the Laptop table.

Output:

EMPLOYEEID	FIRST_NAME	SALARY	CITY	LAPTOPID	NAME
1002	VAIBHAV	60000	NOIDA	L102	HP
3003	DEEPAM	58500	MUMBAI	L104	HP
4002	ASHWINI	54500	NOIDA	L105	DELL
4003	RUCHIKA	60000	DELHI	L106	LENOVO
5001	ARCHIT	55500	DELHI	L107	DELL

Only five employees' records are displayed.

Example 4: Write a query to display employee id, first name, salary, and department from employees table Manager Id and manager name from manager table using inner join where salary > 55000 and department is Oracle.

SELECT EMPLOYEEID, FIRST_NAME, SALARY, DEPARTMENT, M.MANAGERID, M.MANAGER_NAME FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID = M.MANAGERID WHERE SALARY > 55000 AND DEPARTMENT = 'ORACLE';

In the above join query example, we display the employee's details like id, name, salary, and department from the employee's table, manager id, and manager name from the manager table where the manager id from the employees' table equals the manager id from the manager only those employees whose salary is greater than 55000 and Department is Oracle.

Output:

EMPLOYEEID	FIRST_NAME	SALARY	DEPARTMENT	MANAGERID	MANAGER_NAME
1001	VAIBHAVI	65500	ORACLE	1	Snehdeep Kaur
2001	PRACHI	55500	ORACLE	1	Snehdeep Kaur
4003	RUCHIKA	60000	ORACLE	1	Snehdeep Kaur

Only three employees whose salary is greater than 55000 and the department is Oracle.

Example 5: Execute a join query on the Employees’, Manager and, the Laptop table, and display employees’ details, manager id, and laptop id.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, M.MANAGERID, L.LAPTOPID FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID = M.MANAGERID INNER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID; 

The above query displays employee id, first name, last name, manager id, and laptop id from employees, manager, and laptop tables. We join three tables. Employee id is a common column between employees and the laptop table and creates a parent-child relationship between these three tables. Between employees and managers, table manager id is the common column.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	MANAGERID	LAPTOPID
4003	RUCHIKA	AGARWAL	1	L106
3003	DEEPAM	JAUHARI	3	L104
4002	ASHWINI	BAGHAT	3	L105
5001	ARCHIT	SHARMA	4	L107
1002	VAIBHAV	SHARMA	5	L102

Left Outer Join

The left outer join will return all the table records on the left side of the join and matching records for the table on the right side of the join. The result-set contains null for the records that are not common on the right side.

Syntax of left outer join: 

SELECT        Table_Name1.Colum_Name1,     Table_Name1.Column_Name2, Table_Name1.Column_Name3,Table_Name2.Column_Name1,Table_Name2.Column_Name2, Table_Name2.Column_Name3, FROM Table_Name1 LEFT OUTER JOIN Table_Name2 ON Table_Name1.Column_Name = Table_Name2.Column_Name;

Consider the existing tables, which have the following records:

Table 1: Employees

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

Table 2: Manager

Managerid	manager_name	manager_department
1	Snehdeep Kaur	ORACLE
2	Kirti Kirtane	FMW
3	Abhishek Manish	JAVA
4	Anupam Mishra	TESTING
5	Akash Kadam	C#

Table 3: Laptop

LAPTOPID	NAME	EMPLOYEEID
L101	DELL	NULL
L102	HP	1002
L103	LENOVO	NULL
L104	HP	3003
L105	DELL	4002
L106	LENOVO	4003
L107	DELL	5001
L108	HP	NULL
L109	DELL	NULL
L110	HP	NULL

Examples of SQL Left Outer Join

Example 1: Execute a left outer join query on Employees' table and Laptop table, where display employee id, employee name, and laptop id.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, L.LAPTOPID, L.NAME FROM EMPLOYEES E LEFT OUTER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID; 

In the above query, we displayed the employee id, first name, last name from the employee's table laptop id, and name from the laptop table and performed a left outer join query on these two tables. The query will return null values on the right side of the table if the rows are not similar between tables.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	LAPTOPID	NAME
1001	VAIBHAVI	MISHRA	NULL	NULL
1002	VAIBHAV	SHARMA	L102	HP
1003	NIKHIL	VANI	NULL	NULL
2001	PRACHI	SHARMA	NULL	NULL
2002	BHAVESH	JAIN	NULL	NULL
2003	RUCHIKA	JAIN	NULL	NULL
3001	PRANOTI	SHENDE	NULL	NULL
3002	ANUJA	WANRE	NULL	NULL
3003	DEEPAM	JAUHARI	L104	HP
4001	RAJESH	GOUD	NULL	NULL
4002	ASHWINI	BAGHAT	L105	DELL
4003	RUCHIKA	AGARWAL	L106	LENOVO
5001	ARCHIT	SHARMA	L107	DELL

Example 2: Write a query to fetch employee id, first name, last name, salary from employee’s table left outer join on laptop table where salary > 55000.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, L.LAPTOPID, L.NAME FROM EMPLOYEES E LEFT OUTER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID WHERE SALARY > 55000;

In the above query, we fetched the employee details from the employees' table and laptop details from the laptop table, but only those employee details where salary is greater than 55000. We performed the left outer join query on these two tables. The query will return null values on the right side of the table if the rows are not similar between tables.

Output: 

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	LAPTOPID	NAME
1001	VAIBHAVI	MISHRA	65500	NULL	NULL
1002	VAIBHAV	SHARMA	60000	L102	HP
2001	PRACHI	SHARMA	55500	NULL	NULL
2002	BHAVESH	JAIN	65500	NULL	NULL
3001	PRANOTI	SHENDE	55500	NULL	NULL
3003	DEEPAM	JAUHARI	58500	L104	HP
4001	RAJESH	GOUD	60500	NULL	NULL
4003	RUCHIKA	AGARWAL	60000	L106	LENOVO
5001	ARCHIT	SHARMA	55500	L107	DELL

Right Outer Join

The right outer join will return all the table records on the right side of the join and matching records for the table on the left side of the join. The result-set contains null for the records that are not common on the left side.

Syntax of right outer join: 

SELECT        Table_Name1.Colum_Name1,     Table_Name1.Column_Name2, Table_Name1.Column_Name3,Table_Name2.Column_Name1,Table_Name2.Column_Name2, Table_Name2.Column_Name3, FROM Table_Name1 RIGHT OUTER JOIN Table_Name2 ON Table_Name1.Column_Name = Table_Name2.Column_Name;

Examples of SQL Right Outer Join        

Example 1: Execute a right outer join query on employees’ table and laptop table, where display employee id, name of the employees’.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, L.LAPTOPID, L.NAME FROM EMPLOYEES E Right OUTER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID; 

In the above query, we displayed the employee id, first name, last name from the employee's table laptop id, and name from the laptop table and performed the right outer join query on these two tables. The query will return null values on the left side of the table if the rows are not similar between tables.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	LAPTOPID	NAME
NULL	NULL	NULL	L101	DELL
1002	VAIBHAV	SHARMA	L102	HP
NULL	NULL	NULL	L103	LENOVO
3003	DEEPAM	JAUHARI	L104	HP
4002	ASHWINI	BAGHAT	L105	DELL
4003	RUCHIKA	AGARWAL	L106	LENOVO
5001	ARCHIT	SHARMA	L107	DELL
NULL	NULL	NULL	L108	HP
NULL	NULL	NULL	L109	DELL
NULL	NULL	NULL	L110	HP

Example 2: Write a query to fetch employee id, first name, and last name from the employee's table. Right outer join on laptop table where laptop name includes one of the lists is Dell, HP.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, L.LAPTOPID, L.NAME FROM EMPLOYEES E RIGHT OUTER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID WHERE L.NAME IN ('DELL', 'HP');

In the above query, we displayed the employees’ details from the employee's table laptop details on the laptop table. We performed the right outer join query on these two tables only that employee whose laptop name includes one of the lists is Dell and HP. The query will return null values on the left side of the table if the rows are not similar between tables.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	LAPTOPID	NAME
NULL	NULL	NULL	L101	DELL
1002	VAIBHAV	SHARMA	L102	HP
3003	DEEPAM	JAUHARI	L104	HP
4002	ASHWINI	BAGHAT	L105	DELL
5001	ARCHIT	SHARMA	L107	DELL
NULL	NULL	NULL	L108	HP
NULL	NULL	NULL	L109	DELL
NULL	NULL	NULL	L110	HP

Full Outer Join

Full Outer Join merges the result of both the Left Outer Joins and the Right Outer Join. Full Outer Join is the same as Cross Join. Full Outer Joins returns the output, where rows are common or uncommon between the tables.

Syntax of full outer join: 

SELECT COLUMNNAME1, COLUMNNAME2 FROM TABLE1 FULL JOIN TABLE2;

Example 1: Write a query to display employee id, first name, last name from manager id from employee’s table full outer join Manager Table where employee salary is greater than 65000.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, M.MANAGERID FROM EMPLOYEES FULL JOIN MANAGER M WHERE SALARY > 65000;

In the above query, we displayed the employees’ details and manager details from the employees and manager table where the employee salary is greater than 65000 using full join. In full, join each Row of the employee's table across each Row of the manager's table, i.e., the Cartesian product will happen. M * N

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	MANAGERID
1001	VAIBHAVI	MISHRA	1
1001	VAIBHAVI	MISHRA	2
1001	VAIBHAVI	MISHRA	3
1001	VAIBHAVI	MISHRA	4
1001	VAIBHAVI	MISHRA	5
2002	BHAVESH	JAIN	1
2002	BHAVESH	JAIN	2
2002	BHAVESH	JAIN	3
2002	BHAVESH	JAIN	4
2002	BHAVESH	JAIN	5

As we can see, employee id 1001 cross joins with each manager id, same with employee id 2002.

Example 2: Execute a full outer join query on the Employees' table and the laptop table, where employee salary is greater than 60000, and employee location is Pune.

SELECT LAPTOPID, E.EMPLOYEEID, E.FIRST_NAME, E.SALARY, E.CITY FROM LAPTOP FULL JOIN EMPLOYEES E WHERE SALARY > 60000 AND CITY ='PUNE';

In the above query, we display laptop id, employee id, first name, salary, and city from laptop full join employees only those whose salary is greater than 60000 and city is Pune. If both conditions match for employee-only, then the record is fetched.

Output:

Cross Join

Cross joins in SQL are nothing but called a cartesian product. Each Row of one table is combined or merged with each Row of the other table. M * N is cross Join

Syntax of Cross join:

SELECT COLUMNNAME1, COLUMNNAME2 FROM TABLE1 CROSS JOIN TABLE2;

Example 1: Write a query to perform cross join operation on employees table and manager table where employee first name starts with A

SELECT EMPLOYEEID, FIRST_NAME, M.MANAGERID FROM EMPLOYEES CROSS JOIN MANAGER M WHERE FIRST_NAME LIKE 'A%';

In the above full outer join query example, we retrieved the employee details and manager details from the employee table and manager table but only those records where the employee's first name begins with the letter 'A'.

Output:

The result shows that employee id is a cross join with each manager id row.

M * N Here, M = 3; employee id from employee tables

N = 5; manager id from manager table 3 * 5 = 15 rows.

Example 2: Write a query to perform cross join operation on laptop table and employees table where employee salary is greater than 58000 and Mumbai.

SELECT LAPTOPID, NAME, E.EMPLOYEEID FROM LAPTOP CROSS JOIN EMPLOYEES E WHERE SALARY > 58000 AND CITY ='MUMBAI';

In the above query, we displayed the laptop id, name, and employee id from the laptop table to perform cross join operation on the employees' table where the employee salary is greater than 58000 and the city is Pune. Cross join performs like M * N.

Output: