🔸 SQL RIGHT JOIN – Retrieve Non-Matching Records from the Right Table

In relational databases, JOIN operations are powerful tools for combining rows from two or more tables. One lesser-used but crucial variant is the RIGHT JOIN (or RIGHT OUTER JOIN), which ensures that all rows from the right table are returned, even if there are no matching rows in the left table.

This guide will walk you through the syntax, use cases, visual illustrations, examples, and best practices of using RIGHT JOIN in SQL.

---

📘 What is RIGHT JOIN?

The RIGHT JOIN keyword returns all records from the right table, and the matched records from the left table. If there is no match, the result is NULL on the side of the left table.

This is the opposite of LEFT JOIN, where all records from the left table are returned.

---

🧾 RIGHT JOIN Syntax

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

• table1 – left table
• table2 – right table (all rows will be returned from here)
• common_column – a field used to match records

---

📊 RIGHT JOIN Diagram

TABLE1         TABLE2
--------       --------
id name        id department

Right Join ON id:
Returns ALL from TABLE2, and matching from TABLE1

Result:
[Matched rows] + [Unmatched rows from TABLE2 with NULLs from TABLE1]

🟢 = Rows returned
🔴 = NULLs in unmatched columns

---

🧪 RIGHT JOIN Example

🗃️ Tables:

Employees Table (Left Table):

emp_id	name	dept_id
1	Alice	101
2	Bob	102
3	Charlie	NULL

Departments Table (Right Table):

dept_id	dept_name
101	HR
102	Finance
103	IT

🔍 Query:

SELECT 
  employees.name,
  departments.dept_name
FROM 
  employees
RIGHT JOIN 
  departments
ON 
  employees.dept_id = departments.dept_id;

🧾 Output:

name	dept_name
Alice	HR
Bob	Finance
NULL	IT

🔸 The row for IT from the Departments table is included even though no employee is in that department.

---

🔁 RIGHT JOIN vs LEFT JOIN

Feature	LEFT JOIN	RIGHT JOIN
Return All Rows From	Left Table	Right Table
NULLs Appear On	Right Table (if no match)	Left Table (if no match)
Common Usage	More commonly used	Less common, can be replaced with LEFT JOIN by switching table order
Example Use	Get all employees and their departments	Get all departments and employees in them

---

🎯 Use Cases of RIGHT JOIN

• List all entities from the right table regardless of left-side matches.
• Reporting unused or unmatched data (e.g., products without orders, departments without employees).
• Reverse equivalent of LEFT JOIN when table order needs to be preserved.

---

✅ Summary

Keyword	Purpose
RIGHT JOIN	Returns all rows from right table with matched left
NULL	Shows where no matching left row exists
Use Case	Finding unmatched data from left table

RIGHT JOIN is an essential tool for preserving right-table data and detecting missing relationships on the left.

---

❓ Frequently Asked Questions (FAQ)

Q1. Is RIGHT JOIN the same as LEFT JOIN?

No, but they are logical opposites. RIGHT JOIN returns all rows from the right table, while LEFT JOIN returns all from the left.

---

Q2. Can RIGHT JOIN be replaced by LEFT JOIN?

Yes. You can swap the tables and use LEFT JOIN instead of RIGHT JOIN.

Example:

SELECT ...
FROM departments
LEFT JOIN employees
ON departments.dept_id = employees.dept_id;

---

Q3. Does RIGHT JOIN affect performance?

Not significantly more than other joins. But JOIN type + indexing affects performance. Use EXPLAIN plans to check.

---

Share Now :