SQL RIGHT JOIN — Keep All Rows from the Right Table

The office wants every recorded result, including a result whose student record is missing from the sample data.

A RIGHT JOIN preserves the rows from its right source and fills unmatched left fields with NULL. Swapping the sources allows the same preservation rule to be written as a LEFT JOIN.

Identify the preserved source before reading the condition. In the retained example, results on the right remain even when students on the left do not match. Rewriting the query with results on the left of a LEFT JOIN often makes the relationship easier to follow without changing the intended pairs.

Related concepts: LEFT JOIN and FULL OUTER 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.

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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.

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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.

• 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.

Support depends on engine and version. Modern SQLite supports RIGHT JOIN, but older installations may not. A production foreign key might prevent the orphan result used to demonstrate the sample; that does not change how the join works on this teaching fixture.

Use a LEFT JOIN with results as the preserved source, and include the orphan roll number 4.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT r.roll_no, s.name, r.marks FROM practice_results AS r LEFT JOIN practice_students AS s ON s.roll_no = r.roll_no ORDER BY r.roll_no;

Roll number 4 belongs to the result fixture but not the student fixture. Preserving results therefore includes it with a NULL student name.

A RIGHT JOIN preserves the rows from its right source and fills unmatched left fields with NULL. Swapping the sources allows the same preservation rule to be written as a LEFT JOIN.

Roll number 4 belongs to the result fixture but not the student fixture. Preserving results therefore includes it with a NULL student name.

Continue your understanding with LEFT JOIN and FULL OUTER JOIN.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.