Topic 29 of 52
RIGHT OUTER JOIN
Overview
RIGHT JOIN returns ALL rows from the right table plus matching rows from the left table. It is the mirror of LEFT JOIN and is less commonly used — most developers rewrite RIGHT JOINs as LEFT JOINs by swapping table order for clarity.
Syntax
sql
-- RIGHT JOIN: all rows from RIGHT table (products), matching from LEFT
SELECT p.name, COUNT(oi.id) AS times_ordered
FROM order_items oi
RIGHT JOIN products p ON oi.product_id = p.id
GROUP BY p.id, p.name;
-- Includes products with 0 orders (oi.id = NULL)
-- Equivalent LEFT JOIN (same result, more readable)
SELECT p.name, COUNT(oi.id) AS times_ordered
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;
-- RIGHT JOIN to find departments with no employees
SELECT d.name AS dept, e.name AS employee
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;
-- Departments with no employees appear with NULL in employee column
-- Anti-join with RIGHT JOIN
SELECT d.* FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id
WHERE e.id IS NULL; -- departments with NO employeesCommon Pitfalls
- RIGHT JOIN is functionally identical to swapping the tables and using LEFT JOIN — prefer LEFT JOIN for consistency and readability.
- Most SQL style guides recommend avoiding RIGHT JOIN because it makes query flow harder to follow.
- Interview tip: If you see a RIGHT JOIN, mentally swap the table order and convert it to a LEFT JOIN for easier understanding.
Real-World Example
Finding products never sold and categories with no products:
example
sql
-- Products that have never been ordered (RIGHT JOIN approach)
SELECT
p.id,
p.name,
p.price,
p.created_at AS added_date,
EXTRACT(DAYS FROM NOW() - p.created_at) AS days_in_catalog
FROM order_items oi
RIGHT JOIN products p ON oi.product_id = p.id AND oi.created_at >= '2025-01-01'
WHERE oi.product_id IS NULL
AND p.is_active = TRUE
ORDER BY days_in_catalog DESC;
-- Equivalent using LEFT JOIN (preferred style)
SELECT
p.id, p.name, p.price
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL AND p.is_active = TRUE;