SQL RIGHT JOIN: Syntax, Examples, and When to Use It

A RIGHT JOIN returns all rows from the right table and matching rows from the left table. Where no match exists in the left table, left-side columns contain NULL. It is the mirror image of a LEFT JOIN.

Syntax

SELECT columns
FROM left_table
RIGHT JOIN right_table ON left_table.key = right_table.key;

How It Works

-- All departments, including those with no employees
SELECT d.department_name, e.employee_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

Result: Every department appears. Departments with no employees show NULL for employee_name.

Why Most Developers Avoid RIGHT JOIN

In practice, RIGHT JOIN is rarely used because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order:

-- RIGHT JOIN
SELECT d.department_name, e.employee_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- Equivalent LEFT JOIN (preferred by convention)
SELECT d.department_name, e.employee_name
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id;

Both produce identical results. The LEFT JOIN version is preferred because:

1. Reading order matches logic (start with "all departments," then "add employees if they exist")
2. Industry convention (almost all SQL examples use LEFT JOIN)
3. Easier to chain multiple joins (all going the same direction)

When RIGHT JOIN Makes Sense

1. You Cannot Change Table Order

In complex queries with multiple joins, sometimes reordering would break the logical flow:

SELECT o.order_id, oi.product_id, p.product_name, c.category_name
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
RIGHT JOIN categories c ON p.category_id = c.id;
-- Show all categories, even those with no products ordered

2. Readability in Specific Contexts

When the narrative is "show everything from this second table regardless of matches":

-- "Show me all calendar months, with revenue where available"
SELECT m.month_name, COALESCE(r.revenue, 0) AS revenue
FROM revenue_summary r
RIGHT JOIN calendar_months m ON r.month = m.month_date
WHERE m.year = 2026;

Practical Examples

Find Categories Without Products

SELECT c.category_name
FROM products p
RIGHT JOIN categories c ON p.category_id = c.id
WHERE p.id IS NULL;

Complete Report with All Time Periods

SELECT
    cal.date,
    COALESCE(SUM(o.amount), 0) AS daily_revenue
FROM orders o
RIGHT JOIN (
    SELECT generate_series('2026-01-01'::date, CURRENT_DATE, '1 day') AS date
) cal ON DATE(o.order_date) = cal.date
GROUP BY cal.date
ORDER BY cal.date;

RIGHT JOIN vs LEFT JOIN Comparison

Summary

RIGHT JOIN preserves all rows from the right table. It is functionally equivalent to a LEFT JOIN with table order reversed. Most SQL practitioners rewrite RIGHT JOINs as LEFT JOINs for consistency and readability. Use RIGHT JOIN only when reordering tables would make a complex multi-join query harder to read.