SQL Right Join

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.

![SQL Right Join Illustration](/uploads/images/20240424/3904c34301aca9838854d650ff916a7d.png "SQL Right Join Illustration")

<div class="callout callout-info mb-3">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.</div>

To understand this clearly, let's look at the following employees and departments tables.

```sql
+--------+--------------+------------+---------+
| 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 |
+--------+--------------+------------+---------+
```
Table: employees
```sql
+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+
```
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.

```sql
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;
```

<div class="callout callout-success mb-3">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.</div>

After executing the above command, you'll get the output something like this:

<div class="callout callout-info mb-3">Note: If there is a row in the right table but no match in the left table, then the associated result row contains NULL values for all columns coming from the left table.</div>