SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records. It returns NULL if there is no match.

Syntax

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

Example: FULL OUTER JOIN

SELECT employees.name, departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;

Sample Tables

Consider the following two tables:

Employees Table

| id | name        | department_id |
|----|-------------|---------------|
| 1  | John Doe    | 1             |
| 2  | Jane Smith  | 2             |
| 3  | Mike Johnson| 1             |
| 4  | Emily Davis | 3             |

Departments Table

| id | department_name |
|----|-----------------|
| 1  | HR              |
| 2  | Sales           |
| 3  | IT              |
| 4  | Marketing       |

Result of FULL OUTER JOIN

| name        | department_name |
|-------------|-----------------|
| John Doe    | HR              |
| Mike Johnson| HR              |
| Jane Smith  | Sales           |
| Emily Davis | IT              |
| NULL        | Marketing       |

All employees and all departments are returned. Marketing has no matching employee, so name is NULL.