SQL FULL JOIN

In this tutorial, we will learn how to use FULL JOIN or FULL OUTER JOIN Keyword and join two or more tables using SQL.

SQL FULL JOIN Keyword

SQL FULL JOIN keyword Return all the records when there is a match either in a left table or right table. All the unmatched rows or records will be filled with NULL Values.

SQL FULL JOIN or RIGHT FULL JOIN both are the same.

Note:- In some databases, FULL JOIN is called FULL OUTER JOIN.

Syntax - RIGHT JOIN or RIGHT OUTER JOIN Keyword

SELECT column1, column2,...columnN
FROM tableName1
FULL JOIN tableName2 ON tableName1.column1 = tableName2.column1;

Example:

Let us consider three tables "Employee", "Department" and "Designation" for records

Table Name: Employee

ID	EmpName	City	Country	Gender	Salary	DeptID	DesigID
1	Shankar	Delhi	India	male	25000	1	6
2	Sourabh	Delhi	India	male	30000	1	2
3	Ranvijay	Mumbai	India	male	15000	2	3
4	Kapil	Noida	India	male	25000	3	4
5	Shalini	Jaipur	India	female	18000	NULL	NULL
6	Rakesh	Faridabad	India	male	23000	1	2
7	Akshay	Mumbai	India	male	21000	2	4
8	Sarah	New York	US	female	76000	NULL	1
9	Rocky	Noida	India	male	28000	NULL	NULL

Table: Department

DeptID	DepartmentName
1	IT
2	Finance
3	HR

Table: Designation

DesigID	DesignationName
1	GM
2	Manager
3	CEO
4	Developer
5	Team Leader
6	Executive

Example of FULL JOIN or FULL OUTER JOIN keyword

For selecting an employee name, department name and designation name, a query will be:-

Example - FULL JOIN or FULL OUTER JOIN Keyword

SELECT EMP.ID,EMP.EmpName,Dept.DepartmentName,Desg.DesignationName 
FROM Employee EMP 
FULL JOIN Department Dept ON EMP.DeptId=Dept.DeptID 
FULL JOIN Designation Desg ON EMP.DesignationId=Desg.DesigID

Output

ID	EmpName	DepartmentName	DesignationbName
1	Shankar	IT	Executive
2	Sourabh	IT	CEO
3	Ranvijay	Finance	Manager
4	Kapil	HR	Developer
5	Shalini	NULL	NULL
6	Rakesh	IT	Manager
7	Akshay	HR	Developer
8	Sarah	NULL	GM
9	Rocky	NULL	NULL