SQL RIGHT JOIN

A RIGHT JOIN combines records from two tables while keeping all records from the right table.

SELECT *
FROM students
RIGHT JOIN courses
ON students.course_id = courses.id;

All courses are returned. If a course does not have a matching student, the student columns contain NULL.

The basic syntax is:

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

The second table is called the right table.

Suppose we have students and courses tables.

SELECT
    students.student_name,
    courses.course_name
FROM students
RIGHT JOIN courses
ON students.course_id = courses.id;

Every course is displayed, even if no student is enrolled in it.

The ON clause defines how the two tables are related.

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

Here, students.course_id is matched with courses.id.

It is better to select only the required columns when working with larger tables.

SELECT
    students.student_name,
    students.mobile,
    courses.course_name
FROM students
RIGHT JOIN courses
ON students.course_id = courses.id;

Table aliases make RIGHT JOIN queries shorter and easier to read.

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

Here, s represents the students table and c represents the courses table.

If there is no matching record in the left table, the left table columns contain NULL.

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

A course without a matching student will have NULL in student_name.

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

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

This returns courses that currently have no matching student.

A WHERE condition can be applied to a RIGHT JOIN result.

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

This displays active courses and any matching student information.

The result can be sorted using ORDER BY.

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

Multiple conditions can be placed in the ON clause.

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

Every course is kept, while only active students are matched.

RIGHT JOIN can be combined with other joins to retrieve related information.

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

All courses remain in the result.

GROUP BY can be used with RIGHT JOIN to create course reports.

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

Courses without students will have a count of zero.

HAVING can filter grouped RIGHT JOIN results.

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

This finds courses without students.

RIGHT JOIN can keep every course while displaying payment information where available.

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

RIGHT JOIN can help identify courses that currently have no students.

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

These courses exist in the courses table but have no matching student.

CASE can be used to display a meaningful status.

SELECT
    c.course_name,
    CASE
        WHEN s.id IS NULL THEN 'No Student'
        ELSE 'Student Assigned'
    END AS course_status
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id;

COALESCE() can replace NULL values with a readable value.

SELECT
    COALESCE(s.student_name, 'No Student') AS student,
    c.course_name
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id;

If a course has no student, No Student is displayed.

Date conditions can be applied to joined data.

SELECT
    s.student_name,
    c.course_name,
    s.admission_date
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id
WHERE s.admission_date >= '2026-01-01'
   OR s.admission_date IS NULL;

The NULL condition keeps courses without matching students.

The IN operator can be used to filter courses.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id
WHERE c.course_name IN ('Python', 'Java', 'PHP');

The query returns the selected courses and any matching students.

Calculations can be performed using columns from the joined tables.

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

RIGHT JOIN is commonly used with tables connected through primary and foreign keys.

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

The student's foreign key is matched with the course primary key.

The related columns do not need to have the same name.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_code = c.code;

The important point is that the values represent the same relationship.

RIGHT JOIN: Keeps all records from the right table.

LEFT JOIN: Keeps all records from the left table.

-- RIGHT JOIN
SELECT *
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

In this example, every course is kept.

RIGHT JOIN: Keeps every record from the right table.

INNER JOIN: Keeps only records with matches in both tables.

-- RIGHT JOIN
SELECT *
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

If a course has no matching student, the course is still returned.

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

The student_name value will be NULL for a course without a matching student.

A common mistake is forgetting which table is the right table.

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

Here, courses is the right table, so all courses are preserved.

A RIGHT JOIN can usually be rewritten as a LEFT JOIN by reversing the table order.

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

The equivalent LEFT JOIN is:

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

A course report can display every course and the number of students enrolled.

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

Courses with no students are also included.

The following example creates a complete course report using students, courses, and payments.

SELECT
    c.id AS course_id,
    c.course_name AS course,
    COALESCE(s.student_name, 'No Student') AS student,
    COALESCE(s.mobile, 'Not Available') AS mobile,
    COALESCE(p.amount, 0) AS paid_amount,
    CASE
        WHEN s.id IS NULL THEN 'No Student'
        WHEN p.id IS NULL THEN 'Payment Pending'
        ELSE 'Payment Available'
    END AS status
FROM students AS s
RIGHT JOIN courses AS c
ON s.course_id = c.id
LEFT JOIN payments AS p
ON s.id = p.student_id
ORDER BY c.course_name;

This query keeps every course and displays matching student and payment information when available.