🔁 SQL `RIGHT JOIN` – Keep All Rows from the Right Table (Even Without Matches)

SQL RIGHT JOIN helps you retrieve all rows from the right table, even if they don’t have matching entries in the left table.

It’s a powerful SQL join type used when you want to preserve all records from the second (right) table in a join, regardless of whether there’s a related record in the first (left) table.

📘 What is SQL `RIGHT JOIN`?

A RIGHT JOIN, also known as RIGHT OUTER JOIN, combines rows from two tables and:

✅ Returns all rows from the right table
✅ Returns matching rows from the left table
❌ If no match is found on the left, it fills the left columns with NULL

🧾 SQL RIGHT JOIN Syntax

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

table1: left table
table2: right table (this is the one whose rows are always included)

✅ Example: Orders and Shippers

You have two tables:

orders

order_id	shipper_id
1	1
2	2

shippers

shipper_id	shipper_name
1	UPS
2	FedEx
3	DHL

SELECT o.order_id, s.shipper_name
FROM orders o
RIGHT JOIN shippers s
ON o.shipper_id = s.shipper_id;

🧾 Result:

order_id	shipper_name
1	UPS
2	FedEx
NULL	DHL

📌 Even though DHL has no orders, it still appears in the result.

🧪 Use Cases of SQL RIGHT JOIN

Find all products, including those not sold
List all departments, even those without employees
Display all branches, even if no transaction has occurred
Show all campaigns, even if no leads were generated

🔁 RIGHT JOIN vs LEFT JOIN

Feature	`LEFT JOIN`	`RIGHT JOIN`
Returns	All left + matching right rows	All right + matching left rows
No Match Behavior	`NULL` on the right	`NULL` on the left
Use Case	When the left table is primary	When the right table is primary

📌 You can swap table positions to achieve similar logic using LEFT JOIN.

🔍 Practical Examples of RIGHT JOIN

1️⃣ Employees and Departments (Even Empty Departments)

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

🎯 Includes all departments, even if they have no employees.

⚠️ Common RIGHT JOIN Mistakes

❌ Mistake	✅ Solution
Confusing LEFT vs RIGHT direction	Think in terms of which table must be complete
Using WHERE incorrectly	Use `IS NULL` to detect missing matches
Expecting symmetrical results	RIGHT JOIN behaves differently than INNER JOIN

💡 Pro Tip: Use `RIGHT JOIN` to Find Unused Records

SELECT s.shipper_name
FROM orders o
RIGHT JOIN shippers s
ON o.shipper_id = s.shipper_id
WHERE o.order_id IS NULL;

🎯 Returns shippers not used in any order

📝 Summary

RIGHT JOIN returns all rows from the right table
Matched rows from the left table are also returned
Unmatched rows from the left table return as NULL

Use it when you need complete data from the right table, especially in data auditing, reporting, or analysis where missing matches still matter.

Right Join

🔁 SQL RIGHT JOIN – Keep All Rows from the Right Table (Even Without Matches)

📘 What is SQL RIGHT JOIN?