RIGHT JOIN in SQL

➡ī¸ The RIGHT JOIN (also called RIGHT OUTER JOIN) returns all rows from the right table and the matching rows from the left table. If there is no matching row in the left table, SQL returns NULL for the left-table columns.

📖 What is a RIGHT JOIN?

A RIGHT JOIN keeps every record from the right table, regardless of whether a matching record exists in the left table. It is useful when the right table contains the primary data you want to preserve while retrieving related information from the left table whenever available.

Information

The right table is the table written after the RIGHT JOINkeyword, while the left table is the table written before it.

đŸŽ¯ Why Use RIGHT JOIN?

Use a RIGHT JOIN when every record from the right table is important, even if related data from the left table is missing.

  • 📌 Include all records from the right table.
  • 📌 Identify records that have no matching data in the left table.
  • 📌 Generate complete reports from the right table.
  • 📌 Work with optional relationships between tables.

📊 Sample Tables

Consider the following Students table:

StudentIDNameDepartment
101AliceComputer Science
102BobMathematics
103CharliePhysics
104DavidComputer Science

Consider the following Courses table:

CourseIDStudentIDCourseName
1101Database Systems
2101Operating Systems
3103Physics Lab
4104Computer Networks
5105Artificial Intelligence

📝 Basic Syntax

RIGHT JOIN Syntax

SELECT table1.column_name,
       table2.column_name
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

💡 Basic RIGHT JOIN Example

Retrieve every course along with the student enrolled in it, if one exists.

Basic RIGHT JOIN

SELECT Students.Name,
       Courses.CourseName
FROM Students
RIGHT JOIN Courses
ON Students.StudentID = Courses.StudentID;

Result:

NameCourseName
AliceDatabase Systems
AliceOperating Systems
CharliePhysics Lab
DavidComputer Networks
NULLArtificial Intelligence

Important

The course Artificial Intelligence appears even though there is no matching student with StudentID = 105. Student Bob does not appear because there is no matching course.

🏷ī¸ Using Table Aliases

Aliases make join queries shorter and easier to read.

RIGHT JOIN with Aliases

SELECT s.Name,
       c.CourseName
FROM Students AS s
RIGHT JOIN Courses AS c
ON s.StudentID = c.StudentID;

🔍 RIGHT JOIN with WHERE

Filter the joined results using the WHERE clause.

Filter RIGHT JOIN Results

SELECT s.Name,
       c.CourseName
FROM Students s
RIGHT JOIN Courses c
ON s.StudentID = c.StudentID
WHERE c.CourseName LIKE '%Systems%';

đŸšĢ Finding Unmatched Rows

A common use of RIGHT JOIN is finding records in the right table that have no matching rows in the left table.

Courses Without Students

SELECT c.CourseName
FROM Students s
RIGHT JOIN Courses c
ON s.StudentID = c.StudentID
WHERE s.StudentID IS NULL;

This query returns courses that are not assigned to any student.

📊 RIGHT JOIN with ORDER BY

Sort the joined results using the ORDER BY clause.

Sort RIGHT JOIN Results

SELECT s.Name,
       c.CourseName
FROM Students s
RIGHT JOIN Courses c
ON s.StudentID = c.StudentID
ORDER BY c.CourseName ASC;

🔗 RIGHT JOIN Multiple Tables

You can join multiple related tables in a single query.

RIGHT JOIN Three Tables

SELECT s.Name,
       c.CourseName,
       d.DepartmentName
FROM Students s
RIGHT JOIN Courses c
ON s.StudentID = c.StudentID
RIGHT JOIN Departments d
ON c.DepartmentID = d.DepartmentID;

📊 How RIGHT JOIN Works

Students TableCourses TableIncluded?
Matching StudentIDMatching StudentID✅ Yes
No Matching StudentCourse Exists✅ Yes (Student columns are NULL)
Student ExistsNo Matching Course❌ No

⚖ī¸ LEFT JOIN vs RIGHT JOIN

FeatureLEFT JOINRIGHT JOIN
Returns All Rows FromLeft tableRight table
Unmatched Left Rows✅ Included❌ Excluded
Unmatched Right Rows❌ Excluded✅ Included

⚠ī¸ Database Compatibility

Important

Most major database systems such as SQL Server, PostgreSQL, Oracle, and MySQL support RIGHT JOIN. However, some databases, including SQLite, do not support RIGHT JOIN. In such cases, you can usually rewrite the query using a LEFT JOIN by swapping the table order.

⚠ī¸ Common Mistakes

  • ❌ Confusing the left and right tables.
  • ❌ Using the wrong join condition.
  • ❌ Forgetting the ON clause.
  • ❌ Using = NULL instead of IS NULL.

Warning

Many developers prefer using LEFT JOIN because it often makes queries easier to read. A RIGHT JOIN can usually be rewritten as an equivalent LEFT JOIN by reversing the table order.

đŸ’ŧ Real-World Example

A university administrator wants a report listing every course, including courses that have not yet been assigned to a student.

Course Assignment Report

SELECT c.CourseID,
       c.CourseName,
       s.Name AS StudentName
FROM Students s
RIGHT JOIN Courses c
ON s.StudentID = c.StudentID
ORDER BY c.CourseName;

This query ensures that every course appears in the report, even if no student is currently enrolled.

⚠ī¸ Best Practices

Best Practice

Use RIGHT JOIN when all records from the right table must be included. If it improves readability, consider rewriting the query as a LEFT JOIN by reversing the table order. Always join using related keys, qualify columns with table aliases, and use IS NULL to identify unmatched records.

🚀 Key Points to Remember

  • 📌 RIGHT JOIN returns all rows from the right table.
  • 📌 Matching rows from the left table are included.
  • 📌 Unmatched left-table columns contain NULL.
  • 📌 It is useful for finding records in the right table without matches.
  • 📌 Many RIGHT JOIN queries can be rewritten as LEFT JOIN queries.
  • 📌 Use IS NULL to identify unmatched records.
>>"A RIGHT JOIN guarantees that every row from the right table appears in the result, whether a matching row exists or not."

Summary

✅ The RIGHT JOIN is an important SQL join that returns every row from the right table while including matching data from the left table when available. Although it is less commonly used than LEFT JOIN, it is valuable when the right table represents the primary dataset that must be preserved in the query results.