RIGHT JOIN

Introduction

You have now seen INNER JOIN (only matches) and LEFT JOIN (everything from the left table). RIGHT JOIN is the third of this family, and it is best understood as simply the mirror image of LEFT JOIN.

This lesson explains what RIGHT JOIN does, works through an example, and explains why many developers rarely reach for it in practice.

What is a RIGHT JOIN?

A RIGHT JOIN returns ALL rows from the right table (the one named after the JOIN keyword), along with matching rows from the left table. When there is no match on the left side, the columns that would have come from the left table are filled with NULL instead.

RIGHT JOIN and RIGHT OUTER JOIN mean the same thing in MySQL, exactly like LEFT JOIN and LEFT OUTER JOIN.

The Sample Tables

To make RIGHT JOIN's behavior obvious, we add one more enrollment row that points to a student_id that does not exist in the students table — imagine a data entry error, or a student who was deleted.

-- students
-- id | name
--  1 | Alex
--  2 | Sam
--  3 | Priya
--  4 | Owen

INSERT INTO enrollments VALUES (105, 9, 'Data Structures');

-- enrollments now also contains:
-- id  | student_id | course_name
-- 105 | 9          | Data Structures

Notice student_id 9 does not exist in the students table at all.

Basic Syntax

SELECT columns
FROM left_table
RIGHT JOIN right_table
    ON left_table.column = right_table.column;

Here, every row of right_table is guaranteed to appear in the result at least once, regardless of whether left_table has a matching row.

Worked Example

Let's list every enrollment along with the student's name, using RIGHT JOIN so that even the orphaned enrollment (student_id 9) shows up.

SELECT s.name, e.course_name
FROM students AS s
RIGHT JOIN enrollments AS e
    ON s.id = e.student_id;

+-------+-----------------+
| name  | course_name     |
+-------+-----------------+
| Alex  | MySQL Basics    |
| Alex  | Web Design      |
| Sam   | MySQL Basics    |
| Priya | JavaScript 101  |
| NULL  | Data Structures |
+-------+-----------------+

Every row from enrollments made it into the result, including the last one — even though there is no matching student, MySQL fills the name column with NULL rather than dropping the row. Notice, too, that Owen does not appear here, since he has no enrollment row and RIGHT JOIN only guarantees rows from the table on the right.

RIGHT JOIN as a Flipped LEFT JOIN

Here is the key practical insight: any RIGHT JOIN can be rewritten as an equivalent LEFT JOIN, just by swapping the order the two tables are written in. Both queries below produce exactly the same result.

-- Using RIGHT JOIN
SELECT s.name, e.course_name
FROM students AS s
RIGHT JOIN enrollments AS e
    ON s.id = e.student_id;

-- The exact same result using LEFT JOIN, tables swapped
SELECT s.name, e.course_name
FROM enrollments AS e
LEFT JOIN students AS s
    ON s.id = e.student_id;

+-------+-----------------+
| name  | course_name     |
+-------+-----------------+
| Alex  | MySQL Basics    |
| Alex  | Web Design      |
| Sam   | MySQL Basics    |
| Priya | JavaScript 101  |
| NULL  | Data Structures |
+-------+-----------------+

All that changed is which table is listed first in FROM — the table you want "all rows guaranteed" from moves to the FROM position, and the join keyword flips from RIGHT to LEFT.

Why Most Developers Prefer LEFT JOIN

Since RIGHT JOIN can always be rewritten as a LEFT JOIN, many developers and teams standardize on using LEFT JOIN everywhere, and simply reorder their FROM clause instead of ever writing RIGHT JOIN. The reasoning is mostly about consistency and readability.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

RIGHT JOIN behaves exactly like LEFT JOIN, just with the guaranteed table on the other side. Since it can always be rewritten as a LEFT JOIN by reordering the tables, it is more important to recognize RIGHT JOIN when you see it than to write it yourself.

In this lesson, you learned what RIGHT JOIN does, worked through an example with an orphaned enrollment row, and saw exactly how to convert a RIGHT JOIN into an equivalent LEFT JOIN. Next, you will learn CROSS JOIN, which works very differently — it does not use an ON clause at all.