MySQL RIGHT JOIN Clause

The RIGHT JOIN keyword in MySQL returns all the records from the right table and the matched records from the left table. If there is no match, the result is NULL on the left side. This can be especially useful when you want to retrieve all records from one table (the right table), even if there are no matching records in the other table (the left table).

Syntax 

The standard syntax for the RIGHT JOIN clause in MySQL is:

SELECT table1.column1, table2.column2,...
FROM table1 
RIGHT JOIN table2 
ON table1.matching_column = table2.matching_column;

In this syntax: 

table1 and table2: These are the names of the tables you wish to join. 

table1.column1, table2.column2, ...: These are the columns you wish to select. 

matching_column: This is the common column that both tables share, which is used to match rows between the two tables.

Demo Database 

For the demonstration, we'll use our SchoolDB database. Our focus will be on the Students table and the Courses table. 

The Students table includes student_id, first_name, last_name, grade_level columns:

The Courses table includes course_id, course_name, and student_id columns:

Examples 

With our demo database prepared, let's explore some examples of the RIGHT JOIN clause: 

Basic RIGHT JOIN 

To retrieve a list of courses (course_name) and any students (first_name, last_name) enrolled in them:

SELECT Students.first_name, Students.last_name, Courses.course_name
FROM Students 
RIGHT JOIN Courses 
ON Students.student_id = Courses.student_id;

This statement will return a list of all courses and the respective students enrolled in them. If a course does not have any students enrolled, the student's name fields will be NULL. 

RIGHT JOIN with Additional Conditions 

You can pair a WHERE clause with the RIGHT JOIN statement to filter results. 

For example, to find all courses and any 12th-grade students enrolled in them:

SELECT Students.first_name, Students.last_name, Courses.course_name
FROM Students 
RIGHT JOIN Courses 
ON Students.student_id = Courses.student_id
WHERE Students.grade_level = 12;

This statement will fetch all courses and any 12th-grade students enrolled in them. If a course doesn't have any 12th-grade students enrolled, the student's name fields will be NULL. 

When to Use RIGHT JOIN

Use RIGHT JOIN when you want to retrieve all rows from the right table and the matching rows from the left table. Rows from the right table with no match in the left table will have NULL in left table's columns.

Scenario: If you have tables Employees and Salaries, and you wish to list all salaries and the employees receiving them (including any unassigned salaries), a RIGHT JOIN on employee ID could be useful.

Summary 

• Returns all rows from the right table and the matched rows from the left table. 
• If there's no match, the result will contain NULL for every column of the left table. 
• Note: RIGHT JOIN is not supported in some databases, but its effect can be achieved by switching the table positions in a LEFT JOIN.