Introduction
Information in a database is often distributed across related tables. When these tables are combined, a report may need to include records that have no corresponding entry in another table.
RIGHT JOIN supports this requirement by preserving every row from the right-hand table during the join. Understanding which table is preserved, how missing matches appear, and where conditions are placed helps produce accurate results.
What Is a RIGHT JOIN?
A RIGHT JOIN, also called a RIGHT OUTER JOIN, combines rows from two tables according to a join condition. It includes every matching pair and preserves unmatched rows from the right table.
Its basic behavior is:
A match exists: Combine the right-table row with each matching left-table row.
No match exists: Keep the right-table row and fill the left-table columns with
NULL.An unmatched row exists only in the left table: Exclude that row.
The right table is the table written immediately after RIGHT JOIN. Its position in the SELECT column list does not determine which table is preserved.
RIGHT JOIN and RIGHT OUTER JOIN have the same meaning; OUTER is optional. A later WHERE condition can still remove rows from the joined result.
Example Tables
Consider a college database containing two tables.
The Student table stores each student’s identifier, name, assigned department, and semester.
Student
StudentID | Name | DeptID | Semester |
|---|---|---|---|
101 | Asha | 10 | 2 |
102 | Ravi | 20 | 4 |
103 | Meera | 10 | 2 |
104 | Neha | NULL | 2 |
The Department table stores the departments offered by the college, including departments that currently have no students.
Department
DeptID | DeptName |
|---|---|
10 | Computer Science |
20 | Electronics |
30 | Mathematics |
StudentID is the primary key of Student, and DeptID is the primary key of Department. A non-NULL value in Student.DeptID refers to an existing department.
Asha and Meera belong to Computer Science, while Ravi belongs to Electronics. Neha has no assigned department, represented by NULL. Mathematics exists in the department table but has no students.
Using RIGHT JOIN to Include Every Department
Objective: Display every department together with its students, including departments that have no students.
Consider the following queries to list all departments and their matching student records.
SELECT d.DeptID, d.DeptName, s.StudentID, s.NameFROM Student AS sRIGHT JOIN Department AS d ON s.DeptID = d.DeptIDORDER BY d.DeptID, s.StudentID;Here, AS s and AS d assign temporary aliases, or shorter names, to Student and Department. For example, s.DeptID means the DeptID column from Student.
The ON condition matches equal department IDs. ORDER BY d.DeptID, s.StudentID sorts by department ID first and student ID within each department, both in ascending order.
Expected result
DeptID | DeptName | StudentID | Name |
|---|---|---|---|
10 | Computer Science | 101 | Asha |
10 | Computer Science | 103 | Meera |
20 | Electronics | 102 | Ravi |
30 | Mathematics | NULL | NULL |
The result demonstrates three important points:
Multiple matches produce multiple rows: Computer Science appears twice because it matches two students.
Unmatched right-table rows remain: Mathematics appears with
NULLstudent details.Unmatched left-table rows are excluded: Neha does not appear because her department ID does not match a department.
The displayed DeptID comes from Department, so Mathematics retains its ID, 30. Selecting s.DeptID instead would show NULL for that row because no student matched it.
These NULL values are produced in the query result; the join does not insert or modify records in either source table.
HOW RIGHT JOIN WORKS
Finding Departments Without Students
Objective: Return only departments that have no matching student.
A right join already preserves these departments with NULL values in the student columns. Checking the student’s primary key identifies those unmatched rows.
Consider the following queries to find departments without students.
SELECT d.DeptID, d.DeptNameFROM Student AS sRIGHT JOIN Department AS d ON s.DeptID = d.DeptIDWHERE s.StudentID IS NULLORDER BY d.DeptID;Expected result
DeptID | DeptName |
|---|---|
30 | Mathematics |
IS NULL checks whether a value is NULL. Since an actual student’s primary key cannot be NULL, s.StudentID IS NULL identifies rows for which no student matched.
Use IS NULL for this check, rather than = NULL. Ordinary equality comparisons involving NULL do not evaluate to true.
FINDING UNMATCHED DEPARTMENTS
RIGHT JOIN Compared with LEFT JOIN
The difference is the side whose unmatched rows are preserved.
Join type | Rows preserved during the join |
|---|---|
| Matching pairs only |
| Every row from the left table, with matching right-table data |
| Every row from the right table, with matching left-table data |
A RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table positions and retaining the same matching condition and selected columns.
Objective: Produce the original department-and-student report using an equivalent left join.
Consider the following queries to preserve Department by placing it on the left.
SELECT d.DeptID, d.DeptName, s.StudentID, s.NameFROM Department AS dLEFT JOIN Student AS s ON s.DeptID = d.DeptIDORDER BY d.DeptID, s.StudentID;Expected result
DeptID | DeptName | StudentID | Name |
|---|---|---|---|
10 | Computer Science | 101 | Asha |
10 | Computer Science | 103 | Meera |
20 | Electronics | 102 | Ravi |
30 | Mathematics | NULL | NULL |
Both versions preserve Department and produce the same report. Changing only RIGHT JOIN to LEFT JOIN without swapping the tables would preserve students instead.
Summary
RIGHT JOIN preserves every row from the right table during the join and combines it with matching rows from the left table. Missing matches produce NULL values in left-table columns, while multiple matches produce multiple result rows.
The placement of conditions matters: ON controls matching, and WHERE filters the joined result. Checking a non-nullable left-table key with IS NULL finds unmatched records, while counting that key supports reports that include zero matches.
Be the first to add a comment.