SQL Server FULL OUTER JOIN Tutorial

What is FULL OUTER JOIN?

FULL OUTER JOIN returns all rows from the left table and all rows from the right table. When rows in one table do not have matching rows in the other, the result includes NULLs for the missing side.

This join type is useful when you want to see all records from both tables and identify unmatched rows.

• Includes matched rows from both tables.
• Includes unmatched rows from the left table with NULLs for right table columns.
• Includes unmatched rows from the right table with NULLs for left table columns.

Syntax of FULL OUTER JOIN

The basic syntax for FULL OUTER JOIN in SQL Server is:

You specify the two tables to join and the condition on which to join them.

Example of FULL OUTER JOIN

Consider two tables: Employees and Departments. We want to list all employees and all departments, showing NULLs where an employee is not assigned to a department or a department has no employees.

Understanding the Result

The FULL OUTER JOIN query returns all employees and all departments.

Employees without a department show NULL in DepartmentName.

Departments without employees show NULL in EmployeeName.

• Alice and Bob have matching departments, so their department names appear.
• Charlie has no department, so DepartmentName is NULL.
• Finance department has no employees, so EmployeeName is NULL.