MySQL RIGHT JOIN

Last updated: October 19, 2026Oct 19, 2026 3 min read

RIGHT JOIN is the mirror image of LEFT JOIN: it returns every row from the right table, plus matching rows from the left. Unmatched left-hand columns are NULL. Any RIGHT JOIN can be written as a LEFT JOIN by swapping the tables, which is why many teams use only LEFT JOIN.

1. What RIGHT JOIN Returns

SELECT columns
FROM left_table
RIGHT [OUTER] JOIN right_table ON left_table.col = right_table.col;

2. A Basic RIGHT JOIN

SELECT s.name, d.dept_name
FROM students s
RIGHT JOIN departments d ON d.dept_id = s.dept_id
ORDER BY d.dept_name, s.name;

Result: 5 rows

History has no students, so it appears once with name NULL. Diya, who has no department, is not shown, because she is on the left side.

3. Rewriting as a LEFT JOIN

Swap the table order and the result is identical:

SELECT s.name, d.dept_name
FROM departments d
LEFT JOIN students s ON s.dept_id = d.dept_id
ORDER BY d.dept_name, s.name;

Result: 5 rows

4. Finding Unmatched Right Rows

SELECT c.title AS course_without_students
FROM enrolments e
RIGHT JOIN courses c ON c.course_id = e.course_id
WHERE e.course_id IS NULL;

Result: 1 row

5. Counting per Right-Side Row

SELECT d.dept_name, COUNT(s.student_id) AS students
FROM students s
RIGHT JOIN departments d ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY students DESC;

Result: 3 rows

6. Building a FULL OUTER JOIN

MySQL has no FULL OUTER JOIN. Combine LEFT and RIGHT joins with UNION to keep unmatched rows from both sides:

SELECT s.name, d.dept_name FROM students s LEFT  JOIN departments d ON d.dept_id = s.dept_id
UNION
SELECT s.name, d.dept_name FROM students s RIGHT JOIN departments d ON d.dept_id = s.dept_id
ORDER BY dept_name IS NULL, dept_name, name;

Result: 6 rows

Both Diya (no department) and History (no students) appear.

7. LEFT vs RIGHT vs INNER

8. Summary

• RIGHT JOIN keeps every row of the right table; unmatched left columns are NULL.
• A RIGHT JOIN B = B LEFT JOIN A; most code uses LEFT JOIN.
• WHERE left.key IS NULL finds right rows without a match.
• LEFT JOIN UNION RIGHT JOIN emulates FULL OUTER JOIN.