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.

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

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.

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;

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.