SQL FULL JOIN statement

In this tutorial, you will learn how to retrieve data from two tables using SQL Full Join.

the use of full join

FULL JOIN returns all rows from the joined tables, regardless of whether they match, which means that a full join combines LEFT JOINandRIGHT JOINfunction. A full join isa type of outer join, therefore it is also calledfull outer join.

The following Venn diagram illustrates how a full join works.

To clearly understand this, let's take a look at the followingemployeesanddepartmentsTable.

+--------+--------------+------------+---------+
| 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　　　　　　　　　　　　|
+---------+------------------+

Now, suppose you only want to retrieve the names of all employees and the names of available departments, regardless of whether they have corresponding rows in the other table. In this case, you can use a full join, as shown below.

The following statement retrieves all department details and all employee details by connecting the employee and department tables using the generic 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 (such as Oracle, MySQL) do not support full joins. In this case, you can use the UNION ALL operator to combine LEFT JOIN and RIGHT JOIN, as shown 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;

After executing the above command, you will get the following output:

+--------+--------------+------------+------------------+
| 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 results includedepartmentsandemployeesAll rows in the table.