A right join (RIGHT JOIN) is a right outer join, which returns all records from the right table and the matching records from the left table whose join fields are equal.
Background information
An outer join (OUTER JOIN) compares data in two tables using comparison operators. The join result includes not only rows that meet the join conditions but also rows that do not.
Outer joins include full join (FULL JOIN), left join (LEFT JOIN), and right join (RIGHT JOIN). An outer join returns all rows that meet the join conditions, and returns unmatched rows from one table with NULL filled in the corresponding positions of the other table.
This topic provides examples of right joins. For examples of full joins, see FULL JOIN, and for examples related to left joins, see LEFT JOIN.
RIGHT JOIN syntax
An outer join statement generally includes a left table and a right table. The table appearing farthest to the left in the JOIN clause is the left table, and the table appearing farthest to the right is the right table.
The syntax of RIGHT JOIN is as follows:
SELECT select_list FROM table_name1 RIGHT JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ];
Here, table_name1 is the left table, and table_name2 is the right table.
Examples
Create sample tables and insert appropriate data.
obclient [info]> CREATE TABLE tbl_a(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
obclient [info]> CREATE TABLE tbl_b(number INT NOT NULL PRIMARY KEY, value INT);
Query OK, 0 rows affected
obclient [info]> CREATE TABLE tbl_c(id INT NOT NULL , name VARCHAR(50));
Query OK, 0 rows affected (0.081 sec)
obclient [info]> INSERT INTO tbl_a VALUES(1,'ab'),(2,'cd'),(3,'ef'),(4,'gh');
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
obclient [info]> INSERT INTO tbl_b VALUES(1,1001),(3,1003),(5,1005);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient [info]> INSERT INTO tbl_c VALUES(1,'aa'),(2,'dd'),(6,'ee'),(7,'hh'),(9,'xx');
Query OK, 5 rows affected (0.005 sec)
Records: 5 Duplicates: 0 Warnings: 0
Simple right join query
If the data returned by JOIN includes not only the data that meets the join and filter conditions but also the data in the right table that meets its own filter conditions but does not meet the join conditions, use a right outer join (RIGHT OUTER JOIN), which can also be abbreviated as a right join (RIGHT JOIN). If the result of a right join contains no matching data from the left table, the columns from the left table return NULL.
Example 1: Perform a right join query on tables tbl_a and tbl_b and obtain the result.
obclient> SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
obclient> SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
obclient> SELECT * FROM tbl_a RIGHT JOIN tbl_b ON tbl_a.id=tbl_b.number;
+------+------+--------+-------+
| id | name | number | value |
+------+------+--------+-------+
| 1 | ab | 1 | 1001 |
| 3 | ef | 3 | 1003 |
| NULL | NULL | 5 | 1005 |
+------+------+--------+-------+
3 rows in set
Complex right join query
Example:
obclient> SELECT * FROM tbl_a a INNER JOIN tbl_b b RIGHT JOIN tbl_c c ON c.id=b.number ON a.name=c.name;
Empty set (0.009 sec)
Combined use of right join and WHERE clause
You can use a right join to obtain the join result, then use the WHERE clause to filter the join result.
Example 2: After performing a right join query on tables tbl_a and tbl_b, return the data where value=1003 in table tbl_b.
obclient> SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
obclient> SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
obclient> SELECT * FROM tbl_a RIGHT JOIN tbl_b ON tbl_a.id=tbl_b.number WHERE tbl_b.value=1003;
+------+------+--------+-------+
| id | name | number | value |
+------+------+--------+-------+
| 3 | ef | 3 | 1003 |
+------+------+--------+-------+
1 row in set