SQL Right Join

The RIGHT JOIN (or RIGHT OUTER JOIN) is the exact mirror image of a LEFT JOIN. It returns all records from the right table, and the matched records from the left table. If there is no match for a row on the left side, the query still outputs the right row, but fills the corresponding columns from the left table with NULL values.

While it achieves the same logical outcome as swapping your table order and using a LEFT JOIN, it is highly useful when appending secondary data to an existing multi-join chain.

Basic Syntax Blueprint

The table listed after FROM is the Left Table, and the table listed immediately after RIGHT JOIN is the Right Table.

SELECT t1.column1, t2.column2
FROM table1 AS t1                   -- Left Table (Brings matching data)
RIGHT JOIN table2 AS t2             -- Right Table (Keeps all rows)
  ON t1.common_column = t2.common_column;

A Real-World Walkthrough

Let's look at how our standard employees and departments tables behave when we switch the query to a RIGHT JOIN.

employees Table (Left)

departments Table (Right)

The Right Join Query:

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

The Output Result:

What happened here?

• Finance is fully preserved in the output list even though no employee belongs to it. Because there is no matching row on the left, the employee_name column evaluates to NULL.
• Amit is excluded completely because he only exists in the left table and doesn't map to a valid department.

Industry Best Practice: Left vs. Right Join

In real-world software development and data analysis, LEFT JOIN is preferred over RIGHT JOIN.

Because Western languages read from left to right, structured code is significantly easier to understand when the primary driving table is declared first (at the top/left) and secondary tables are joined onto it.

These two queries perform identically under the hood:

-- Query A (Using Right Join)
SELECT e.name, d.department_name 
FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

-- Query B (Using Left Join - Highly Recommended for Readability)
SELECT e.name, d.department_name 
FROM departments d LEFT JOIN employees e ON d.department_id = d.department_id;

Real-World Use Case: Orphan Categories

Just like the left join, you can use a right join to find missing links by tracking NULL outcomes.

Example: Find departments that currently have zero employees assigned

SELECT d.department_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL; -- Filters to show only unassigned departments