RIGHT JOIN

In the previous lesson, we explored LEFT JOIN. Now we will learn RIGHT JOIN, which behaves in a similar way — but from the opposite side.

Understanding RIGHT JOIN helps complete your knowledge of joins, even though it is used less frequently in practice.

What is RIGHT JOIN?

A RIGHT JOIN returns:

• All rows from the right table
• Matching rows from the left table
• NULL values when no match exists

If there is no matching row in the left table, the right table row still appears in the result.

RIGHT JOIN Syntax

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

The table written after RIGHT JOIN is the right table.

Example Tables

employees (Left Table)

+----+----------+---------------+--------+
| id | name     | department_id | salary |
+----+----------+---------------+--------+
| 1  | Alice    | 101           | 75000  |
| 2  | Bob      | 102           | 72000  |
| 3  | Charlie  | 101           | 60000  |
+----+----------+---------------+--------+
  

departments (Right Table)

+---------------+----------------+
| department_id | department_name|
+---------------+----------------+
| 101           | IT             |
| 102           | HR             |
| 103           | Finance        |
| 104           | Legal          |
+---------------+----------------+
  

RIGHT JOIN Example

Retrieve all departments and any employees assigned to them.

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

Result explanation:

• IT → Alice, Charlie
• HR → Bob
• Finance → NULL
• Legal → NULL

Finance and Legal appear even though they have no employees.

RIGHT JOIN vs LEFT JOIN

RIGHT JOIN is essentially the mirror image of LEFT JOIN.

Why RIGHT JOIN Is Rarely Used

In practice, developers prefer LEFT JOIN because:

• Queries read left-to-right naturally
• LEFT JOIN logic is easier to reason about
• RIGHT JOIN can always be rewritten as LEFT JOIN

Equivalent LEFT JOIN version of the previous query:

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

Same result — clearer intent.

Finding Unmatched Right Rows

RIGHT JOIN can be used to find rows that have no match in the left table.

Example: Departments without employees.

SELECT d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
  

Common Beginner Mistakes

• Confusing left and right tables
• Filtering NULLs incorrectly
• Using RIGHT JOIN when LEFT JOIN is clearer
• Assuming RIGHT JOIN is mandatory to learn first

When Should You Use RIGHT JOIN?

Use RIGHT JOIN when:

• You inherit existing queries using it
• You want all rows from the second table explicitly
• You understand join direction clearly

Otherwise, prefer LEFT JOIN for readability.

What’s Next?

In the next lesson, we will explore FULL JOIN and understand how it combines LEFT and RIGHT joins.