MySQL | RIGHT JOIN
Start your free 7-days trial now!
MySQL's RIGHT JOIN clause returns all rows from the right table and matching records from the left table. NULL will be used for records that have no matching record in the left table. RIGHT JOIN is also referred to as RIGHT OUTER JOIN.
Syntax
SELECT column_name(s)FROM left_tableRIGHT JOIN right_tableON left_table.column = right_table.column;
Examples
Consider the following table about products bought by some customers:
id | product | bought_by |
|---|---|---|
1 | computer | 1 |
2 | hair dryer | 3 |
The above sample table can be created using the code here.
Also consider the following table containing customer information:
id | name | age |
|---|---|---|
1 | alex | 15 |
2 | bob | 25 |
3 | cathy | 30 |
The above sample table can be created using the code here.
Basic usage
To perform a RIGHT JOIN with product as the left table and customer as the right (main) table:
SELECT c.id, c.name, c.age, p.name AS product_name FROM product pRIGHT JOIN customer cON c.id = p.bought_by;
+----+-----------+------+--------------+| id | name | age | product_name |+----+-----------+------+--------------+| 1 | alex | 15 | computer || 3 | cathy | 30 | hair dryer || 2 | bob | 25 | NULL |+----+-----------+------+--------------+
We return all three customer records from the right table customer and the corresponding products they have purchased from product (left table). As bob has not purchased any product, NULL is returned for his corresponding product_name value.
We can also perform RIGHT JOIN using more than two tables. However, there will always be one main table (all its rows will be present in the result), while the others will be secondary (rows will only appear if there are matches else NULL).
