How do you perform a full outer join in SQL?

A FULL OUTER JOIN in SQL is used to return all records from both tables, matching the rows where possible, and filling in NULLs where there’s no match.

In other words, it combines the results of both LEFT JOIN and RIGHT JOIN — you get every record from both tables, even if there’s no relationship between them.

Here’s a simple example:

SELECT 
    e.EmployeeID,
    e.EmployeeName,
    d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

This query will return:

• All employees with their departments (where a match exists).
• Employees who don’t belong to any department (with NULL in DepartmentName).
• Departments that have no employees (with NULL in EmployeeName).

I used this type of join in a data reconciliation project, where we needed to compare records from two different systems — for example, to find which customers exist in one system but not the other. A FULL OUTER JOIN helped us easily identify mismatches on both sides.

However, one limitation is that FULL OUTER JOIN is not supported in all databases — for instance, MySQL doesn’t have direct support. In such cases, I use a combination of LEFT JOIN, RIGHT JOIN, and UNION as an alternative:

SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
UNION
SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

This gives the same result as a FULL OUTER JOIN.

In summary, a FULL OUTER JOIN is very useful when you need to see complete data from both sides — including the unmatched records — especially in data validation, auditing, or reconciliation scenarios.