SQL FULL JOIN

The FULL JOIN returns all rows from the joined tables, whether they are matched or no.

The FULL JOIN is a type of outer join that's why it can be called FULL OUTER JOIN.

The FULL JOIN is a combination of the LEFT JOIN and the RIGHT JOIN.

The following illustration shows how full join works:

Syntax

The syntax of the FULL JOIN is as follows:

SELECT column_list
FROM tableA
FULL JOIN tableB
ON tableA.column_name = tableB.column_name;

Demo Table

To understand the FULL JOIN, let us consider the following "Department" and "Employee" tables:

• Table: Employee:

  +--------+-------------+-----------+-----------+---------+
  | emp_no | first_name  | last_name | salary    | dept_id |
  +--------+-------------+-----------+-----------+---------+
  | 1001   | James       | Smith     | 6600      | 1       |
  | 1002   | Maria       | Martinez  | 9000      | 2       |
  | 1003   | Andrew      | Rodriguez | 9500      | 3       |
  | 1004   | Robert      | Hernandez | 9600      | 2       |
  | 1005   | Donna       | Williams  | 7400      | NULL    |
  | 1006   | James       | Johnson   | 8000      | NULL    |
  | 1007   | Stephanie   | Miller    | 8500      | 8       |
  | 1008   | Nelson      | Lopez     | 7300      | 8       |
  +--------+-------------+-----------+-----------+---------+
  

• Table : Department:

  +---------+------------------+
  | dept_id | dept_name        |
  +---------+------------------+
  | 1       | Customer Service |
  | 2       | Development      |
  | 3       | Finance          |
  | 4       | Human Resources  |
  | 5       | Marketing        |
  | 6       | Production       |
  | 7       | Research         |
  | 8       | Sales            |
  +---------+------------------+
  

SQL FULL JOIN Example

Now, suppose that you need to select all the employees' details and all departments' names, whether there is a match or no in the joined table. To have this result set, you can use a full join.

The following SQL statement selects all the employees' details and all departments' names by joining the "Employee" and "Department" tables using the common column "dept_id" through the FULL JOIN.

The FULL JOIN will also include departments with no employees assigned to them and employees not attached to a department.

SELECT em.emp_no, em.first_name, em.last_name, dep.dept_name
FROM employee AS em
LEFT JOIN department AS dep
ON em.dept_id = dep.dept_id;

After running the above SQL statement, the result set will be as follows:

+--------+-------------+-----------+------------------+
| emp_no | first_name  | last_name | dept_name        |
+--------+-------------+-----------+------------------+
| 1001   | James       | Smith     | Customer Service |
| 1002   | Maria       | Martinez  | Development      |
| 1003   | Andrew      | Rodriguez | Finance          |
| 1004   | Robert      | Hernandez | Development      |
| 1005   | Maria       | Martinez  | NULL             |
| 1006   | Andrew      | Rodriguez | NULL             |
| 1007   | Stephanie   | Miller    | Sales            |
| 1008   | Nelson      | Lopez     | Sales            |
| NULL   | NULL        | NULL      | Human Resources  |
| NULL   | NULL        | NULL      | Marketing        |
| NULL   | NULL        | NULL      | Production       |
| NULL   | NULL        | NULL      | Research         |
+--------+-------------+-----------+------------------+

Note: The FULL JOIN will return all matching rows from both tables whether the other table matches or not. So if there are rows in "Employee" that do not have matches in "Department", or the opposite if there are rows in "Department" that do not have matches in "Employee" , those rows will be listed, and a NULL value would be extended to those fields in the result set.