A FULL OUTER JOIN returns all rows from both tables.

If there is a match, it shows values from both tables.

If there is no match in one table, the missing columns show NULL.

Basic syntax:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Step 1: Example tables

Employees Table

EmployeeID FirstName LastName DepartmentID
1 John Doe 101
2 Jane Smith 102
3 Mike Brown 101
4 Sara White NULL

Departments Table

DepartmentID DepartmentName
101 IT
102 HR
103 Finance

Step 2: Simple FULL OUTER JOIN

Retrieve all employees and all departments, showing NULL when there is no match:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees AS e
FULL OUTER JOIN Departments AS d
ON e.DepartmentID = d.DepartmentID;

Result:

FirstName LastName DepartmentName
John Doe IT
Mike Brown IT
Jane Smith HR
Sara White NULL
NULL NULL Finance

Explanation:

  • Matched rows: IT and HR employees matched with departments.
  • Unmatched left: Sara (no DepartmentID).
  • Unmatched right: Finance department (no employee).

Step 3: FULL OUTER JOIN with aliases

SELECT e.FirstName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;
  • e → Employees
  • d → Departments

Result: Same as Step 3.

Step 4: FULL OUTER JOIN with WHERE clause

You can filter for rows without matches in either table:

SELECT e.FirstName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d
ON e.DepartmentID = d.DepartmentID
WHERE e.FirstName IS NULL OR d.DepartmentName IS NULL;

Result:

FirstName DepartmentName
Sara NULL
NULL Finance

Shows employees without departments and departments without employees.

Step 5: FULL OUTER JOIN with multiple conditions

You can add conditions in the ON clause:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d
ON e.DepartmentID = d.DepartmentID AND d.DepartmentName <> 'HR';

Result:

FirstName LastName DepartmentName
John Doe IT
Mike Brown IT
Sara White NULL
NULL NULL Finance
Jane Smith NULL

Only non-HR departments are considered for matching.

Categorized in:

SQL Server,