RIGHT JOIN
A RIGHT JOIN is a type of outer join that returns all records from the right table and the matching records from the left table. If there are no matching records in the left table, the result will contain NULL values for the left table's columns.
Background information
An outer join is a type of join that returns all rows from both tables, even if there are no matching rows. This is in contrast to an inner join, which only returns rows that have matching values in both tables.
There are three types of outer joins: full outer join, left outer join, and right outer join. A full outer join returns all rows from both tables, a left outer join returns all rows from the left table and the matching rows from the right table, and a right outer join returns all rows from the right table and the matching rows from the left table.
This topic provides examples of right outer joins. For more information about full outer joins, see FULL JOIN. For more information about left outer joins, see LEFT JOIN.
RIGHT JOIN syntax
In an outer join statement, the left table is the one that appears first in the JOIN clause, and the right table is the one that appears last in the JOIN clause.
The syntax for a 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 ];
In this syntax, table_name1 is the left table, and table_name2 is the right table.
Examples
Create sample tables and insert appropriate data.
CREATE TABLE tbl_a(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
CREATE TABLE tbl_b(number INT NOT NULL PRIMARY KEY, value INT);
Query OK, 0 rows affected
CREATE TABLE tbl_c(id INT NOT NULL , name VARCHAR(50));
Query OK, 0 rows affected (0.081 sec)
INSERT INTO tbl_a VALUES(1,'ab'),(2,'cd'),(3,'ef'),(4,'gh');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
INSERT INTO tbl_b VALUES(1,1001),(3,1003),(5,1005);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
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 JOIN clause returns data that includes rows that meet the join and filter conditions, as well as rows in the right table that meet the filter conditions but not the join conditions, you can use a right outer join (RIGHT OUTER JOIN) or simply a right join (RIGHT JOIN). In the result of a right join, if data from the left table does not exist, the corresponding column will return NULL.
Example 1: Perform a right join on tables tbl_a and tbl_b, and retrieve the result.
SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
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:
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)
Using RIGHT JOIN with the WHERE clause
You can use a right join to retrieve the join result, and then use the WHERE clause to filter the join result.
Example 2: Perform a right join on tables tbl_a and tbl_b, and retrieve the result where value=1003 in tbl_b.
SELECT * FROM tbl_a;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | cd |
| 3 | ef |
| 4 | gh |
+------+------+
4 rows in set
SELECT * FROM tbl_b;
+--------+-------+
| number | value |
+--------+-------+
| 1 | 1001 |
| 3 | 1003 |
| 5 | 1005 |
+--------+-------+
3 rows in set
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