MySQL RIGHT JOIN

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

RIGHT JOIN keeps every row of the table on the right, matched or not. It is the mirror image of LEFT JOIN and it does nothing a LEFT JOIN cannot do, so this lesson is short. It exists because you will read it in other people’s SQL. Open the client with mysql -u root -p sakila before you start.

How RIGHT JOIN works

SELECT COLUMN_LIST
FROM LEFT_TABLE
RIGHT JOIN RIGHT_TABLE ON LEFT_TABLE.COLUMN_NAME = RIGHT_TABLE.COLUMN_NAME;

Replace COLUMN_LIST with the columns you want, LEFT_TABLE with the table you start from, RIGHT_TABLE with the table every row of which you want to keep, and each COLUMN_NAME with the column on that side that links them. A right row that matches nothing comes back with every column of the left table set to NULL. RIGHT OUTER JOIN is the same clause spelled out.

Examples

1) Keep every row of the second table

film is on the left and language on the right, so this keeps all 6 languages.

SELECT l.name, f.title
FROM film AS f
RIGHT JOIN language AS l ON f.language_id = l.language_id
ORDER BY l.name, f.title;

+----------+-----------------------------+
| name     | title                       |
+----------+-----------------------------+
| English  | ACADEMY DINOSAUR            |
| English  | ACE GOLDFINGER              |
| English  | ADAPTATION HOLES            |
...
+----------+-----------------------------+
1005 rows in set (0.00 sec)

1,005 rows, the same count the LEFT JOIN lesson got from the same 2 tables in the other order.

2) List the rows that matched nothing

The same pattern as a left join, with the NULL test on the left table now.

SELECT f.title, l.name
FROM film AS f
RIGHT JOIN language AS l ON f.language_id = l.language_id
WHERE f.film_id IS NULL
ORDER BY l.name;

+-------+----------+
| title | name     |
+-------+----------+
| NULL  | French   |
| NULL  | German   |
| NULL  | Italian  |
| NULL  | Japanese |
| NULL  | Mandarin |
+-------+----------+
5 rows in set (0.00 sec)

The 5 languages no film is in.

Every RIGHT JOIN can be written as a LEFT JOIN

a RIGHT JOIN b returns the same rows as b LEFT JOIN a. Swap the 2 table names, swap the keyword, and the answer does not move. With SELECT * the columns come back in a different order, because * expands in the order the tables appear in FROM, so name your columns if the order matters. Most SQL you read is written with LEFT JOIN for that reason: a query is easier to follow when every join in it leans the same way. Everything else about it, including the WHERE trap, works exactly as the LEFT JOIN lesson describes, with left and right exchanged.

Summary

Use RIGHT JOIN to keep every row of the table named second.
Expect NULL in every left-hand column of a row that matched nothing.
Read a RIGHT JOIN b as b LEFT JOIN a, and name your columns rather than using *.
Prefer LEFT JOIN in SQL you write, so the joins in a query all lean the same way.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How RIGHT JOIN works

Examples

1) Keep every row of the second table

2) List the rows that matched nothing

Every RIGHT JOIN can be written as a LEFT JOIN

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How RIGHT JOIN works

​Examples

​1) Keep every row of the second table

​2) List the rows that matched nothing

​Every RIGHT JOIN can be written as a LEFT JOIN

​Summary

​See also

How RIGHT JOIN works

Examples

1) Keep every row of the second table

2) List the rows that matched nothing

Every RIGHT JOIN can be written as a LEFT JOIN

Summary

See also