DocsJoinsRIGHT JOIN

RIGHT JOIN

Joins

Concept

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

In practice, RIGHT JOIN is rarely used because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Most developers prefer LEFT JOIN for consistency and readability. However, understanding RIGHT JOIN is important for reading legacy code and passing SQL certification exams.

The choice between LEFT and RIGHT JOIN is purely a matter of which table you want to preserve in full. If you are starting from orders and want to include all products, you could write products RIGHT JOIN orders or orders LEFT JOIN products — the result is the same.

Syntax

-- Basic right join
SELECT o.order_id, o.order_date, c.first_name
FROM orders o
RIGHT JOIN customers c ON c.customer_id = o.customer_id;

-- Equivalent left join (preferred style)
SELECT o.order_id, o.order_date, c.first_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- All products with their sales data (even unsold ones)
-- Written as RIGHT JOIN for illustration
SELECT
  oi.order_id,
  oi.quantity,
  p.product_id,
  p.name AS product_name,
  p.category
FROM order_items oi
RIGHT JOIN products p ON p.product_id = oi.product_id
ORDER BY p.name;

-- Equivalent LEFT JOIN (preferred)
SELECT
  oi.order_id,
  oi.quantity,
  p.product_id,
  p.name AS product_name,
  p.category
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
ORDER BY p.name;

Common Pitfalls & Tips

1RIGHT JOIN is functionally identical to LEFT JOIN with swapped tables. Prefer LEFT JOIN for consistency across your codebase.
2Mixing LEFT and RIGHT JOINs in the same query makes it very hard to reason about which rows are preserved. Stick to one direction.
3Like LEFT JOIN, placing a filter on the non-preserved table in WHERE converts it to an INNER JOIN. Use the ON clause for such filters.

Practice RIGHT JOIN queries