RIGHT (OUTER) JOIN

A RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of a LEFT JOIN: it returns every row from the right table, regardless of whether it has a match in the left table, filling in NULL for any left-table columns when there is no match. Everything you know about LEFT JOIN applies here — just with the tables swapped.

Worked example

Using the same customers and orders tables, a RIGHT JOIN from customers to orders returns every order, including order 105, which references a customer_id (99) that does not exist in customers.

Every order, with customer details if available

SQL

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o
  ON c.customer_id = o.customer_id
ORDER BY o.order_id;

name   | order_id | amount
-------+----------+-------
Alice  | 101      | 250.00
Alice  | 102      | 75.50
Bob    | 103      | 300.00
Carol  | 104      | 120.00
NULL   | 105      | 45.00

Order 105 still shows up, but with NULL in place of a customer name, because no row in customers has customer_id 99. This is useful for exactly the kind of data-integrity check you would expect: finding "orphan" rows in the right table that do not point to anything real on the left.

Why RIGHT JOIN is rare in practice

RIGHT JOIN can always be rewritten as a LEFT JOIN

Every RIGHT JOIN can be rewritten as an equivalent LEFT JOIN simply by swapping the order of the two tables. Because of this, many teams and style guides recommend avoiding RIGHT JOIN entirely and always writing LEFT JOIN instead — it keeps every query in a codebase reading in the same direction (left table is the base, right table is joined in), which makes queries faster to scan and easier to compare.

The same result, written as a LEFT JOIN

SQL

-- Equivalent to the RIGHT JOIN above, but with tables swapped.
SELECT c.name, o.order_id, o.amount
FROM orders o
LEFT JOIN customers c
  ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Both queries return an identical result set. The only difference is which table is listed first in the FROM clause. Since LEFT JOIN is far more common in real codebases, rewriting a RIGHT JOIN as a LEFT JOIN also makes your query easier for teammates to read at a glance.

RIGHT JOIN keeps every row from the right table, filling in NULL for unmatched left-table columns.
Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order.
Most style guides prefer LEFT JOIN everywhere for consistency, reserving RIGHT JOIN for rare cases.
RIGHT JOIN is useful for spotting rows on the "many" side that reference something missing on the other side.