MySQL RIGHT JOIN

A RIGHT JOIN returns every row from the right table and matching rows from the left table.

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

All courses are displayed. If a course has no matching student, the student columns contain NULL.

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

The table after RIGHT JOIN is the right table, and all of its rows are preserved.

Suppose we have a students table:

And a courses table:

The PHP course does not have a matching student.

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

Result:

PHP is included because courses is the right table.

Consider this query:

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

students is the left table.

courses is the right table.

RIGHT JOIN preserves every row from courses.

Aliases make RIGHT JOIN queries shorter and easier to understand.

SELECT
    s.name,
    c.course_name
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id;

Here, s represents students and c represents courses.

SELECT
    s.student_id,
    s.name,
    s.city,
    c.id AS course_id,
    c.course_name,
    c.fee
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

This displays every course and its matching student information.

If a right-table row has no matching left-table row, the left-table columns contain NULL.

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

For a course without students, s.name will be NULL.

RIGHT JOIN can be used to find courses that do not have matching students.

SELECT
    c.id,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
WHERE s.student_id IS NULL;

This returns courses without matching student records.

WHERE can filter the result of a RIGHT JOIN.

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
WHERE c.status = 'Active';

This keeps courses whose status is Active.

ORDER BY can sort a RIGHT JOIN result.

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
ORDER BY c.course_name ASC;

The results are sorted by course name.

LIMIT can restrict the number of rows returned.

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
LIMIT 10;

This returns a maximum of 10 result rows.

RIGHT JOIN can be combined with other JOIN operations.

SELECT
    s.name,
    c.course_name,
    p.amount
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
LEFT JOIN payments p
ON s.student_id = p.student_id;

This keeps every course while displaying matching student and payment information when available.

RIGHT JOIN can be used to count matching records while keeping all rows from the right table.

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
GROUP BY c.id, c.course_name;

A course without students can show a count of 0.

SUM() can be used with RIGHT JOIN to calculate totals.

SELECT
    c.course_name,
    COALESCE(SUM(p.amount), 0) AS total_paid
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
LEFT JOIN payments p
ON s.student_id = p.student_id
GROUP BY c.id, c.course_name;

COALESCE() converts a NULL total into 0.

GROUP BY is useful for creating course-wise reports.

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
GROUP BY c.id, c.course_name;

All courses remain represented, including courses with zero students.

HAVING can filter grouped RIGHT JOIN results.

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
GROUP BY c.id, c.course_name
HAVING COUNT(s.student_id) > 0;

This displays only courses having at least one matching student.

The ON clause can contain additional conditions.

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
AND s.status = 'Active';

This keeps every course while matching only active students.

With a RIGHT JOIN, placing a condition in the ON clause can preserve unmatched right-table rows.

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
AND s.status = 'Active';

Every course remains in the result, but only active students are matched.

Compare this with:

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
WHERE s.status = 'Active';

The WHERE condition rejects NULL student rows, so courses without an active matching student are removed.

RIGHT JOIN can identify courses without students.

SELECT
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
WHERE s.student_id IS NULL;

This is useful for finding courses that currently have no enrolled student.

RIGHT JOIN can be used to preserve all courses while including payment information from related students.

SELECT
    c.course_name,
    s.name,
    p.amount
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
LEFT JOIN payments p
ON s.student_id = p.student_id;

Courses remain in the result even when there are no students or payments.

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
GROUP BY c.id, c.course_name
ORDER BY total_students DESC;

This creates a course-wise student report while keeping courses with no students.

You can calculate values from the joined tables.

SELECT
    c.course_name,
    c.fee,
    COALESCE(SUM(s.paid_fee), 0) AS total_paid,
    c.fee - COALESCE(SUM(s.paid_fee), 0) AS remaining_amount
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
GROUP BY c.id, c.course_name, c.fee;

This creates a course-level calculation using student payment information.

DISTINCT can be used when unique combinations are required.

SELECT DISTINCT
    c.course_name,
    s.city
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

DISTINCT removes duplicate rows having the same selected values.

A common mistake is placing a condition on the left table in WHERE when you want to preserve unmatched right-table rows.

For example:

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
WHERE s.status = 'Active';

Courses with no matching student have NULL in s.status, so the WHERE condition removes those rows.

If every course should remain, put the condition in ON:

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
AND s.status = 'Active';

RIGHT JOIN can preserve every course while matching payments from a particular period.

SELECT
    c.course_name,
    s.name,
    p.amount,
    p.payment_date
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
LEFT JOIN payments p
ON s.student_id = p.student_id
AND p.payment_date >= '2026-01-01'
AND p.payment_date < '2026-02-01';

All courses remain in the result, while only January 2026 payments are matched.

A simplified logical processing order is:

FROM
RIGHT JOIN
ON
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

Understanding this order helps explain why filtering a left-table column in WHERE can remove NULL rows from a RIGHT JOIN.

For example:

SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

Can be rewritten as:

SELECT
    s.name,
    c.course_name
FROM courses c
LEFT JOIN students s
ON s.course_id = c.id;

These two queries represent the same preservation direction.

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100),
    fee DECIMAL(10,2),
    status VARCHAR(20)
);

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    city VARCHAR(100),
    status VARCHAR(20),
    paid_fee DECIMAL(10,2)
);

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    amount DECIMAL(10,2),
    payment_date DATE
);

INSERT INTO courses
(course_name, fee, status)
VALUES
('Python', 15000, 'Active'),
('Java', 18000, 'Active'),
('PHP', 12000, 'Active'),
('SQL', 10000, 'Active');

INSERT INTO students
(name, course_id, city, status, paid_fee)
VALUES
('Amit Kumar', 1, 'Patna', 'Active', 10000),
('Priya Singh', 2, 'Gaya', 'Active', 18000),
('Rahul Sharma', 1, 'Patna', 'Active', 7000);

INSERT INTO payments
(student_id, amount, payment_date)
VALUES
(1, 5000, '2026-01-10'),
(1, 5000, '2026-02-10'),
(2, 18000, '2026-03-15');

-- Show all courses and matching students
SELECT
    s.name,
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

-- Show courses without students
SELECT
    c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
WHERE s.student_id IS NULL;

-- Course-wise student count
SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
GROUP BY c.id, c.course_name;

-- Course-wise payment total
SELECT
    c.course_name,
    COALESCE(SUM(p.amount), 0) AS total_paid
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
LEFT JOIN payments p
ON s.student_id = p.student_id
GROUP BY c.id, c.course_name;

-- Active students matched with every course
SELECT
    c.course_name,
    s.name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id
AND s.status = 'Active';

This example demonstrates RIGHT JOIN, NULL values, missing records, GROUP BY, COUNT(), SUM(), and multiple JOIN operations.