MySQL - RIGHT JOIN

In this tutorial we will learn about right join in MySQL.

When we use the RIGHT JOIN, it returns all the rows from the right side table and only matched rows from the left side table. If there is no match at the left side table then we get NULL.

The pictorial representation of RIGHT JOIN is given below.

For this tutorial we will be using the employee and comments table.

Employee Table

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com    |     7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02        | John      | Doe      | johndoe@example.com  |     8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com  |     9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e04        | Tin       | Tin      | tintin@example.com   |     6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e05        | Bob       | Coder    | bobcoder@example.com |     7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

Comments Table

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 |
|         3 | e01        | Happy               | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

RIGHT JOIN syntax

SELECT tbl1.column_name, tbl2.column_name
FROM table_1 tbl1
RIGHT JOIN table_2 tbl2
ON tbl1.column_X = tbl2.column_X;

RIGHT JOIN employee and comments table

In the following example we will list all the comments and employees who commented them.

So, for this we will RIGHT JOIN the two tables on the column employeeid. This will fetch us all the rows that have matching employeeid in both the tables.

When performing RIGHT JOIN we get all the rows from the right side table even if there is no match in the left side table.

mysql> SELECT e.employeeid, e.firstname, e.lastname, c.commentid, c.commentbody
FROM employee e
RIGHT JOIN comments c
ON e.employeeid = c.employeeid
ORDER BY employeeid;

Output:

+------------+-----------+----------+-----------+---------------------+
| employeeid | firstname | lastname | commentid | commentbody         |
+------------+-----------+----------+-----------+---------------------+
| e01        | Yusuf     | Shakeel  |         3 | Happy               |
| e01        | Yusuf     | Shakeel  |         5 | Keep coding....     |
| e03        | Jane      | Doe      |         1 | Awesome             |
| e03        | Jane      | Doe      |         2 | Hello World         |
| e04        | Tin       | Tin      |         4 | This is my comment. |
+------------+-----------+----------+-----------+---------------------+
5 rows in set (0.00 sec)