RIGHT JOIN
RIGHT JOIN returns all records from the right table and the matching records from the left table.
If there is no matching record in the left table, the columns from the left table contain NULL values.
What is RIGHT JOIN?
A RIGHT JOIN returns every row from the right table, regardless of whether a matching row exists in the left table.
When a matching record is found, information from both tables is returned. When no match is found, the columns from the left table contain NULL.
RIGHT JOIN Syntax
SELECT columns
FROM Table1
RIGHT JOIN Table2
ON Table1.CommonColumn = Table2.CommonColumn;
Example Tables
Students Table — Left Table
| StudentId | StudentName | CourseId |
|---|---|---|
| 101 | Rahul | 1 |
| 102 | Priya | 2 |
| 103 | Amit | 1 |
| 104 | Neha | 3 |
| 105 | Rohan | 5 |
Courses Table — Right Table
| CourseId | CourseName |
|---|---|
| 1 | .NET Full Stack |
| 2 | Java Full Stack |
| 3 | Python Full Stack |
| 4 | Software Testing |
Basic RIGHT JOIN Example
Here, Students is the left table and Courses is the right table.
SELECT
Students.StudentName,
Courses.CourseName
FROM Students
RIGHT JOIN Courses
ON Students.CourseId = Courses.CourseId;
Result
Because Courses is the right table, all courses are returned, including courses that do not have a matching student.
| StudentName | CourseName |
|---|---|
| Rahul | .NET Full Stack |
| Amit | .NET Full Stack |
| Priya | Java Full Stack |
| Neha | Python Full Stack |
| NULL | Software Testing |
RIGHT JOIN with WHERE
RIGHT JOIN can be combined with a WHERE clause to filter the returned records.
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
RIGHT JOIN Courses AS C
ON S.CourseId = C.CourseId
WHERE C.CourseName LIKE '%Full Stack';
This returns courses whose names contain Full Stack.
Finding Records Without a Match
RIGHT JOIN can be used with IS NULL to find records in the right table that do not have a matching record in the left table.
SELECT
C.CourseId,
C.CourseName
FROM Students AS S
RIGHT JOIN Courses AS C
ON S.CourseId = C.CourseId
WHERE S.StudentId IS NULL;
This returns courses that do not have any matching student.
RIGHT JOIN using Aliases
Table aliases make RIGHT JOIN queries shorter and easier to read.
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
RIGHT JOIN Courses AS C
ON S.CourseId = C.CourseId;
RIGHT JOIN vs LEFT JOIN
| LEFT JOIN | RIGHT JOIN |
|---|---|
| Returns all records from the left table. | Returns all records from the right table. |
| Non-matching right-side values become NULL. | Non-matching left-side values become NULL. |
| Left table is given priority. | Right table is given priority. |
RIGHT JOIN and LEFT JOIN
A RIGHT JOIN can often be rewritten as a LEFT JOIN by reversing the order of the tables.
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
RIGHT JOIN Courses AS C
ON S.CourseId = C.CourseId;
The same result can generally be expressed by placing Courses first and using LEFT JOIN.
SELECT
S.StudentName,
C.CourseName
FROM Courses AS C
LEFT JOIN Students AS S
ON S.CourseId = C.CourseId;
Important Points
- RIGHT JOIN returns every record from the right table.
- Matching records from the left table are included.
- If there is no match, left-table columns contain NULL.
- The ON clause defines the relationship between the tables.
- RIGHT JOIN can be rewritten as a LEFT JOIN by reversing the table order.
Summary
RIGHT JOIN returns all records from the right table and matching records from the left table. When a matching record is not available in the left table, NULL values are returned for the left-table columns.