SQL RIGHT JOIN - Keep All Rows from the Right Table

Introduction

Sometimes the information you care most about sits in the right-hand table. For example, imagine a table of exam results — and you want to make sure every result is included, even if the corresponding student’s info is missing. That’s what RIGHT JOIN is for.

What is RIGHT JOIN?

RIGHT JOIN returns all rows from the right table and the matching rows from the left. If no match is found, NULL values are used for columns from the left table.

Syntax of RIGHT JOIN

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

1. Sample Tables – Students and Results

Table: students

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A'),
(2, 'Diya Iyer', '9B'),
(3, 'Sneha Patil', '10A');

Table: results

CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

INSERT INTO results VALUES
(1, 'Maths', 85),
(2, 'Maths', 92),
(3, 'Maths', 78),
(4, 'Maths', 88); -- roll_no 4 is missing in students

2. Using RIGHT JOIN

Let’s make sure we include every result, even if there's no student record:

SELECT s.name, r.subject, r.marks
FROM students s
RIGHT JOIN results r
ON s.roll_no = r.roll_no;

name           | subject | marks
----------------+---------+-------
Aarav Sharma   | Maths   | 85
Diya Iyer      | Maths   | 92
Sneha Patil    | Maths   | 78
NULL           | Maths   | 88

Explanation: The last row has no matching student record for roll_no 4, so name appears as NULL.

3. Filtering for Unmatched Rows

Want to find results that don’t have a student assigned?

SELECT r.roll_no, r.subject, r.marks
FROM students s
RIGHT JOIN results r ON s.roll_no = r.roll_no
WHERE s.roll_no IS NULL;

roll_no | subject | marks
--------+---------+-------
4       | Maths   | 88

4. Compare with LEFT JOIN

To understand RIGHT JOIN, flip the mental model of LEFT JOIN. This:

SELECT s.name, r.subject, r.marks
FROM students s
RIGHT JOIN results r
ON s.roll_no = r.roll_no;

is the same as:

SELECT s.name, r.subject, r.marks
FROM results r
LEFT JOIN students s
ON s.roll_no = r.roll_no;

So, RIGHT JOIN isn’t “better” or “worse” than LEFT JOIN — just a different direction depending on what table you want to preserve.

Best Practices

• Use RIGHT JOIN when the right table is more important (e.g., results).
• Always check for NULLs when analyzing unmatched rows.
• Use aliases for better readability in JOIN queries.

Summary

RIGHT JOIN ensures you retain all records from the right table even when the left-side data is incomplete. Whether you're auditing exam results, financial transactions, or orders — RIGHT JOIN guarantees you never overlook the unmatched entries on the right.

What’s Next?

Now that you’ve mastered LEFT and RIGHT JOINs, we’ll dive into FULL OUTER JOIN — combining both to show all unmatched and matched data from both sides.