SQL FULL OUTER JOIN

A FULL OUTER JOIN in SQL combines the results of both LEFT JOIN and RIGHT JOIN. It returns all records when there is a match in either left (table1) or right (table2) table records. If there is no match, the result is NULL on the side that does not have a match.

Here's an example to illustrate this concept:

Suppose we have two tables:

Table 1: Employees

Table 2: Departments

Now, let's perform a FULL OUTER JOIN on these tables based on the DeptID column:

SELECT Employees.EmpID, Employees.Name, Employees.DeptID, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Output

Explanation

• John and Jane have matching DeptID in both tables, so their records are fully populated.
• Alice has a DeptID that doesn't match any DeptID in the Departments table, so DeptName is NULL.
• Bob has no DeptID, so both DeptID and DeptName are NULL.
• The Departments table has a DeptID (104) that doesn't match any DeptID in the Employees table, so EmpID and Name are NULL.

This way, FULL OUTER JOIN ensures that all records from both tables are included in the result set, with NULL in places where there is no match.