SQL RIGHT JOIN

8
0

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.Name
FROM Student AS s
RIGHT JOIN Department AS d
ON s.DeptID = d.DeptID
ORDER 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 NULL student 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

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.DeptName
FROM Student AS s
RIGHT JOIN Department AS d
ON s.DeptID = d.DeptID
WHERE s.StudentID IS NULL
ORDER 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

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

INNER JOIN

Matching pairs only

LEFT JOIN

Every row from the left table, with matching right-table data

RIGHT JOIN

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.Name
FROM Department AS d
LEFT JOIN Student AS s
ON s.DeptID = d.DeptID
ORDER 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.

CS Core

Read Similar Blogs

Comments0