SQL Full Join

In this tutorial you will learn how to retrieve data from two tables using SQL full join.

Using Full Joins
A FULL JOIN returns all the rows from the joined tables, whether they are matched or not i.e. you can say a full join combines the functions of a LEFT JOIN and a RIGHT JOIN. Full join is a type of outer join that's why it is also referred as full outer join.

The following Venn diagram illustrates how full join works.

![SQL Full Join Illustration](/uploads/images/20240424/a9a4aa6f00ebedc61f7d06e2ffdcf73e.png "SQL Full Join Illustration")

<div class="callout callout-info mb-3">Note: An outer join is a join that includes rows in a result set even though there may not be a match between rows in the two tables being joined.</div>

To understand this clearly, let's look at the following employees and departments tables.

```sql
+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+
```
Table: employees
```sql
+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+
```
Table: departments

Now, let's say you just want to retrieve the names of all the employees and the names of available departments, regardless of whether they have corresponding rows in the other table, in that case you can use a full join as demonstrated below.

The following statement retrieves all the departments as well as the details of all the employees by joining the employees and departments tables together using the common dept_id field.

```sql
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 FULL JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;
```
Some databases, such as Oracle, MySQL do not support full joins. In that case you can use the UNION ALL operator to combine the LEFT JOIN and RIGHT JOIN as follows:

```sql
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id
UNION ALL
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 RIGHT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;
```
After executing the above command, you'll get the output something like this:

```sql
+--------+--------------+------------+------------------+
| emp_id | emp_name     | hire_date  | dept_name        |
+--------+--------------+------------+------------------+
|   NULL | NULL         | NULL       | Customer Service |
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources  |
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources  |
|      5 | Martin Blank | 2008-06-24 | NULL             |
|      4 | Rick Deckard | 2007-01-03 | Finance          |
|      4 | Rick Deckard | 2007-01-03 | Finance          |
|      3 | Sarah Connor | 2005-10-18 | Sales            |
|      3 | Sarah Connor | 2005-10-18 | Sales            |
|      2 | Tony Montana | 2002-07-15 | Administration   |
|      2 | Tony Montana | 2002-07-15 | Administration   |
+--------+--------------+------------+------------------+
```
As you can see the result includes all the rows from both the departments and employees table.

<div class="callout callout-success mb-3">Tip: In a join query, the left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.</div>

<div class="callout callout-info mb-3">Note: When performing outer joins, wherever the DBMS (Database Management System) can't match any row, it places NULL in the columns to indicate data do not exist.</div>