SQL RIGHT JOIN — Keep All Rows from the Right Table

What Is a RIGHT JOIN?

A RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of a LEFT JOIN. It returns all rows from the right table and the matched rows from the left table. When there is no match on the left side, those columns are filled with NULL.

Syntax

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

How It Works

1. Start with every row in departments (the right table).
2. For each department, find matching rows in employees where e.department_id = d.id.
3. If matches exist, output combined rows.
4. If no employee belongs to a department, output the department row with NULL in the employee columns.

Venn Diagram Interpretation

RIGHT JOIN highlights the entire right circle. The intersection contains matched data, while the right-only region represents departments that have no employees assigned (left columns are NULL).

Why RIGHT JOIN Is Less Common

In practice, most developers rewrite RIGHT JOINs as LEFT JOINs by swapping the table order:

-- Equivalent LEFT JOIN version
SELECT e.name, e.hire_date, d.department_name
FROM departments AS d
LEFT JOIN employees AS e
  ON e.department_id = d.id;

This produces identical results and is considered more readable because the "primary" table appears first in the FROM clause. Some SQL style guides explicitly recommend avoiding RIGHT JOIN in favor of LEFT JOIN for consistency.

When RIGHT JOIN Makes Sense

RIGHT JOIN is genuinely useful in multi-join queries where the table you want to preserve happens to be on the right side of an existing join chain. Rewriting such queries as LEFT JOINs can sometimes require restructuring the entire query, so a RIGHT JOIN keeps things simpler.