RIGHT JOIN in MySQL combines rows from two tables based on a related column, returning all rows from the right table and matching rows from the left table. If there are no matches, NULL values are included for the columns from the left table.
- All Rows from the Right Table
- Matching Rows from the Left Table
students table:
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | Alice | 20 |
| 2 | Bob | 22 |
| 3 | Charlie | 21 |
| 4 | David | 23 |
+----+-----------+-----+courses table:
+----+------------+------------+
| id | name | student_id |
+----+------------+------------+
| 1 | Math | 1 |
| 2 | Physics | 2 |
| 3 | Chemistry | 1 |
| 4 | Biology | 3 |
| 5 | History | 2 |
| 6 | English | NULL |
+----+------------+------------+SELECT courses.name AS course_name, students.name AS student_name, students.age AS student_age
FROM courses
RIGHT JOIN students ON courses.student_id = students.id;In this query:
- We're selecting the course names from the courses table (courses.name), the student names from the students table (students.name), and the student ages from the students table (students.age).
- We're performing a RIGHT JOIN (RIGHT JOIN students) to ensure that all records from the courses table are included in the result, even if there are no matching records in the students table.
- The condition ON courses.student_id = students.id specifies how the tables are joined based on the student_id column in the courses table and the id column in the students table.
+-------------+--------------+-------------+
| course_name | student_name | student_age |
+-------------+--------------+-------------+
| Math | Alice | 20 |
| Physics | Bob | 22 |
| Chemistry | Alice | 20 |
| Biology | Charlie | 21 |
| History | Bob | 22 |
| English | NULL | NULL |
+-------------+--------------+-------------+