SQL RIGHT JOIN

The RIGHT JOIN returns all rows in the right table (table B), and the matched rows from the left table (table A). The result is set to NULL from the left side, where there is no match.

The RIGHT JOIN is a type of outer join that's why it can be called RIGHT OUTER JOIN.

The following illustration shows how right join works:

Syntax

The syntax for the RIGHT JOIN is as follows:

SELECT column_list
FROM tableA
RIGHT JOIN tableB
ON tableA.column_name = tableB.column_name;

Demo Table

To understand the RIGHT JOIN, let us consider the following "Employee" and "Department" tables:

• Table: Employee:

  +--------+-------------+-----------+-----------+---------+
  | emp_no | first_name  | last_name | salary    | dept_id |
  +--------+-------------+-----------+-----------+---------+
  | 1001   | James       | Smith     | 6600      | 1       |
  | 1002   | Maria       | Martinez  | 9000      | 2       |
  | 1003   | Andrew      | Rodriguez | 9500      | 3       |
  | 1004   | Robert      | Hernandez | 9600      | 2       |
  | 1005   | Donna       | Williams  | 7400      | NULL    |
  | 1006   | James       | Johnson   | 8000      | NULL    |
  | 1007   | Stephanie   | Miller    | 8500      | 8       |
  | 1008   | Nelson      | Lopez     | 7300      | 8       |
  +--------+-------------+-----------+-----------+---------+
  

• Table : Department:

  +---------+------------------+
  | dept_id | dept_name        |
  +---------+------------------+
  | 1       | Customer Service |
  | 2       | Development      |
  | 3       | Finance          |
  | 4       | Human Resources  |
  | 5       | Marketing        |
  | 6       | Production       |
  | 7       | Research         |
  | 8       | Sales            |
  +---------+------------------+
  

SQL RIGHT JOIN Example

Now, suppose that you need to select all departments names and employees details working in those departments. And you need the list of all departments even if there is no employee assigned to them.

The following SQL statement selects all the available departments as well as the employee number, first name, last name of the employee who works in that department by joining the "Employee" and the "Department" tables using the common column "dept_id" through the RIGHT JOIN.

The RIGHT JOIN will also include departments that have no employees assigned to them.

SELECT em.emp_no, em.first_name, em.last_name, dep.dept_name
FROM employee AS em
RIGHT JOIN department AS dep
ON em.dept_id = dep.dept_id;

Note: In an SQL statement, the right table is located in the right-hand of the JOIN clause, and the left table is located in the left-hand of the JOIN clause.

After running the above SQL statement, the output will look like the following:

+--------+-------------+-----------+------------------+
| emp_no | first_name  | last_name | dept_name        |
+--------+-------------+-----------+------------------+
| 1001   | James       | Smith     | Customer Service |
| 1002   | Maria       | Martinez  | Development      |
| 1003   | Andrew      | Rodriguez | Finance          |
| 1004   | Robert      | Hernandez | Development      |
| 1007   | Stephanie   | Miller    | Sales            |
| 1008   | Nelson      | Lopez     | Sales            |
| NULL   | NULL        | NULL      | Human Resources  |
| NULL   | NULL        | NULL      | Marketing        |
| NULL   | NULL        | NULL      | Production       |
| NULL   | NULL        | NULL      | Research         |
+--------+-------------+-----------+------------------+

As you can see, all the rows from the "Department" table are included in the result set, even if there is no match on the "dept_id" column in the "Employee" table.

In our example, we can see the departments "Human Resources", "Marketing", "Production", and "Research", even if there is no employee in those departments.

Note: The RIGHT JOIN returns all the rows from the right table, even if there are no matches in the left table.