SQL RIGHT JOIN statement

In this tutorial, you will learn how to use SQL right join to retrieve data from two tables.

Using right join

RIGHT JOIN is equivalent toLEFT JOINcompletely opposite. It returns all rows from the right table and the rows from the left table that meet the join conditions.

Right join isa type of outer joinand is also known asright outer join. Other variants of outer join areLeft joinAndFull outer join. The following Venn diagram illustrates how a right join works.

To clearly understand this, let's look at the followingemployeesAnddepartmentsTable.

+--------+--------------+------------+---------+
| 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, suppose you want to retrieve the names of all departments and the detailed information of the employees working in those departments. But in actual situations, some departments may not have any employees working at the moment. Alright, let's find the answer.

The following statement connects the employee and department tables using the generic dept_id field to retrieve all available departments and the id, name, and hire date of the employees in that department.

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 will get the following output:

+--------+--------------+------------+------------------+
| emp_id | emp_name | hire_date | dept_name |
+--------+--------------+------------+------------------+
|　　　　　　2　| Tony Montana |　2002-07-15　| Administration |
| NULL | 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 |
+--------+--------------+------------+------------------+

A correct join includes all rows from the department table in the result set, regardless of whether the dept_id column in the employee table matches, because you can clearly see that even if there are no employees in the department, 'Customer Service' will also be included.