SQL BASIC
SQL JOINS
SQL ADVANCED
SQL REFERENCE

SQL FULL JOIN Statement

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

Using Full Joins

A FULL JOIN returns all 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

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.

To understand this clearly, let's look at 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, 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.

  • 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:

  • 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:

+--------+--------------+------------+------------------+
| 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 rows from both the departments and employees table.

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.

Note: When performing outer joins, wherever the DBMS can't match any row, it places NULL in the columns to indicate data do not exist.

Your Feedback:

We would love to hear from you, please drop us a line.