(SQL Tutorials for Citizen Data Scientist)
SQL RIGHT JOIN Operation
In this tutorial you will learn how to fetch data from two tables using SQL right join.
Using Right Joins
The RIGHT JOIN
is the exact opposite of the LEFT JOIN
. It returns all rows from the right table along with the rows from the left table for which the join condition is met.
Right join is a type of outer join that’s why it is also referred as right outer join. Other variations of outer join are left join and full join. The following Venn diagram illustrates how right 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 want to retrieve the names of all departments as well as the details of employees who’re working in that department. But, in real situation there may be some department in which currently no employee is working. Well, let’s find out.
The following statement retrieves all the available departments as well as the id, name, hiring date of the employees who belongs to that department 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 RIGHT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY dept_name;
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.
After executing the above command, you’ll get the output something like this:
+--------+--------------+------------+------------------+ | emp_id | emp_name | hire_date | dept_name | +--------+--------------+------------+------------------+ | 2 | Tony Montana | 2002-07-15 | Administration | | NULL | NULL | NULL | Customer Service | | 4 | Rick Deckard | 2007-01-03 | Finance | | 1 | Ethan Hunt | 2001-05-01 | Human Resources | | 3 | Sarah Connor | 2005-10-18 | Sales | +--------+--------------+------------+------------------+
The right join includes all the rows from the departments table in the result set, whether or not there is a match on the dept_id column in the employees table, as you can clearly see the department “Customer Service” is included even if there is no employee in this department.
Beginners Guide to SQL – SQL RIGHT JOIN Operation
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.