The RIGHT JOIN is essentially the opposite of the LEFT JOIN. It retrieves all rows from the right table along with matching rows from the left table based on the join condition.
Right join is classified as an outer join, also known as right outer join. Other types of outer joins include left join and full join. The following Venn diagram illustrates how a right join functions.
Note: An outer join includes rows in the result set even when there's no match between rows from the joined tables.
To better understand this concept, let's examine 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, suppose you want to fetch the names of all departments along with details of employees working in each department. However, in reality, there might be departments where no employees are currently assigned. Let's explore this scenario.
The next statement retrieves all departments along with the employee IDs, names, and hiring dates of employees associated with each department. This is achieved by joining the employees and departments tables 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;
Tip: In a join query, the left table is the one listed first in the JOIN clause, and the right table is the one listed second.
Upon running the command above, the output will resemble the following:
+--------+--------------+------------+------------------+ | 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 incorporates all rows from the departments table in the result set, regardless of whether there is a match on the dept_id column in the employees table. For example, the department "Customer Service" is included even if there are no employees currently assigned to it.
Note: If a row exists in the right table but finds no match in the left table, the corresponding result row will have NULL values for all columns derived from the left table.