(SQL Tutorials for Citizen Data Scientist)
SQL FULL JOIN Statement
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.
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.
Example
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:
Example
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 the rows from both the departments and employees table.
Beginners Guide to SQL – SQL FULL JOIN Statement
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.