RIGHT JOIN

Right Join in MySQL

Introduction
Preserving all records from right table requires RIGHT JOIN functionality.
RIGHT JOIN keeps all right table rows, NULLs for non-matching left table rows.
It is widely used for:

• Detail tables with optional masters
• Reverse optional relationships
• Complete right-side reporting

What is Right Join
RIGHT JOIN returns ALL rows from right table and matching rows from left table.
Non-matching left table rows show NULL values.

Basic Syntax

sql

SELECT columns 

FROM table1 

RIGHT JOIN table2 ON table1.column = table2.column; 

Example

sql

SELECT s.name, c.course_name 

FROM Students s 

RIGHT JOIN Courses c ON s.course_id = c.id; 

All courses + enrolled students (NULL for empty courses).

RIGHT JOIN with WHERE
Filter after join (affects NULL handling).

sql

SELECT c.course_name, s.name 

FROM Students s 

RIGHT JOIN Courses c ON s.course_id = c.id 

WHERE s.name IS NOT NULL;  -- Becomes INNER JOIN 

Multiple RIGHT JOINs
Chain preserving right tables.

sql

SELECT d.department_name, c.course_name, s.name 

FROM Students s 

RIGHT JOIN Courses c ON s.course_id = c.id 

RIGHT JOIN Departments d ON c.dept_id = d.id; 

RIGHT JOIN with ORDER BY
Sort including non-matching rows.

sql

SELECT c.course_name, s.name 

FROM Students s 

RIGHT JOIN Courses c ON s.course_id = c.id 

ORDER BY c.course_name; 

RIGHT JOIN with GROUP BY
Aggregate preserving right table.

sql

SELECT c.course_name, COUNT(s.id) as enrollment 

FROM Students s 

RIGHT JOIN Courses c ON s.course_id = c.id 

GROUP BY c.course_name; 

Shows 0 for empty courses.

RIGHT JOIN IS NULL
Find right table orphans.

sql

SELECT c.course_name 

FROM Students s 

RIGHT JOIN Courses c ON s.course_id = c.id 

WHERE s.id IS NULL;  -- Courses with no students 

RIGHT JOIN with USING
Same column shorthand.

sql

SELECT s.name, c.course_name 

FROM Students s 

RIGHT JOIN Courses c USING(course_id); 

RIGHT vs LEFT Equivalence

sql

-- RIGHT JOIN equivalent 

FROM Students s RIGHT JOIN Courses c ON s.id = c.student_id; 

 

-- Same as LEFT JOIN 

FROM Courses c LEFT JOIN Students s ON s.id = c.student_id; 

RIGHT JOIN Multiple Conditions
Complex matching.

sql

SELECT c.course_name, s.name 

FROM Students s 

RIGHT JOIN Courses c ON s.course_id = c.id 

                 AND s.status = 'Active'; 

Performance with RIGHT JOIN
Same as LEFT JOIN (optimizer converts).

sql

-- MySQL optimizer often converts RIGHT to LEFT 

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id; 

Execution Order with RIGHT JOIN
FROM → RIGHT JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• Optimizer converts RIGHT to LEFT JOIN
• Index both join columns
• WHERE left_table.col = X → INNER JOIN
• Rarely needed (LEFT JOIN + table order sufficient)

Important Notes

• All right table rows preserved
• LEFT JOIN more common/readable
• WHERE left_table.col converts to INNER
• Multiple RIGHT JOINs chain right-to-left
• NULLs for non-matching left rows

Example Scenario
Course utilization report (all courses):

sql

SELECT

    c.course_name,

    c.capacity,

    COUNT(s.id) as enrolled,

    CASE WHEN COUNT(s.id) = 0 THEN 'Empty' ELSE 'Active' END as status

FROM Students s

RIGHT JOIN Courses c ON s.course_id = c.id

GROUP BY c.id, c.course_name, c.capacity

ORDER BY enrolled DESC, c.course_name;

Common Mistakes

• WHERE left_table.col = X (becomes INNER JOIN)
• Using RIGHT when LEFT clearer
• Non-indexed join columns
• Expecting left table preservation
• Multiple RIGHT JOIN order confusion

Key Points to Remember

• RIGHT JOIN preserves ALL right table rows
• Equivalent to LEFT JOIN with tables reversed
• WHERE left_table.col → INNER JOIN
• Use IS NULL for orphan detection
• LEFT JOIN preferred for readability
• Optimizer handles RIGHT→LEFT conversion