MySQL
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.
NULL
The pictorial representation of RIGHT JOIN is given below.
For this tutorial we will be using the employee and comments table.
employee
comments
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)
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)
SELECT tbl1.column_name, tbl2.column_name FROM table_1 tbl1 RIGHT JOIN table_2 tbl2 ON tbl1.column_X = tbl2.column_X;
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.
employeeid
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)