A FULL JOIN retrieves all rows from the joined tables, whether they have matches or not. Essentially, it combines the functionalities of a LEFT JOIN and a RIGHT JOIN. Full join falls under the category of outer joins, also known as full outer join.
The following Venn diagram illustrates how a full join operates.
Note: An outer join includes rows in the result set even when there's no match between rows from the joined tables.
To grasp this concept better, let's examine the following employees and departments tables.
+--------+--------------+------------+---------+ | 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 | +--------+--------------+------------+---------+ |
+---------+------------------+ | dept_id | dept_name | +---------+------------------+ | 1 | Administration | | 2 | Customer Service | | 3 | Finance | | 4 | Human Resources | | 5 | Sales | +---------+------------------+ |
|
Table: employees |
Table: departments |
Now, suppose you want to fetch the names of all employees and the names of all departments, regardless of whether they have corresponding rows in the other table. In such cases, you can utilize a full join, as shown below.
The following statement retrieves all departments along with details of all employees by joining the employees and departments tables using the common dept_id field.
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, like Oracle and MySQL, do not support full joins. In such situations, you can achieve a similar result by using the UNION ALL operator to combine the results of LEFT JOIN and RIGHT JOIN, as demonstrated below:
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;Upon executing the command above, the output will resemble something like this:
+--------+--------------+------------+------------------+ | 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 observed, the result includes all rows from both the departments and employees tables.
Tip: In a join query, the left table is the one listed first in the JOIN clause, and the right table is the one listed second.
Note: During outer joins, when the DBMS (Database Management System) cannot find a matching row, it inserts NULL in the columns to denote the absence of data.