Understanding SQL FULL JOIN

A FULL JOIN (also known as FULL OUTER JOIN) is a type of SQL join that returns all records when there is a match in either the left or right table. It combines the result of both the LEFT JOIN and RIGHT JOIN, returning rows where there’s a match and also the rows from each table without a match. In the case of non-matching rows, NULL values are filled in the missing columns.

What is SQL FULL JOIN?

The FULL JOIN returns all rows from both tables, and where there’s no match, NULL values are filled in for the missing part of the row.

Example: Consider a table of employees and a table of departments. The FULL JOIN will return all employees and all departments, even if some employees don’t belong to any department or some departments don’t have any employees.

Syntax of FULL JOIN

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
  

Example with Indian Data

Let’s use an example where we have two tables: employees and departments.

Employees Table

EmployeeID	EmployeeName	DepartmentID	City
1	Rahul Sharma	101	Mumbai
2	Anjali Verma	102	Delhi
3	Karan Gupta	103	Bangalore
4	Priya Singh	NULL	Kolkata

Departments Table

DepartmentID	DepartmentName	HeadOffice
101	IT	Mumbai
102	HR	Delhi
103	Finance	Bangalore
104	Marketing	Chennai

Query: Fetch Employee and Department Information with FULL JOIN

We can use the following SQL query to perform a FULL JOIN between the employees and departments tables:

SELECT employees.EmployeeID, employees.EmployeeName, employees.City, departments.DepartmentName, departments.HeadOffice
FROM employees
FULL JOIN departments
ON employees.DepartmentID = departments.DepartmentID;
  

Output

EmployeeID	EmployeeName	City	DepartmentName	HeadOffice
1	Rahul Sharma	Mumbai	IT	Mumbai
2	Anjali Verma	Delhi	HR	Delhi
3	Karan Gupta	Bangalore	Finance	Bangalore
4	Priya Singh	Kolkata	NULL	NULL
NULL	NULL	NULL	Marketing	Chennai