MySQL RIGHT JOIN

RIGHT JOIN Key Features

RIGHT JOIN keeps all right table data

Left columns = NULL if no match

A RIGHT JOIN B = B LEFT JOIN A

Can rewrite as LEFT JOIN

🔹 Basic RIGHT JOIN

RIGHT JOIN ensures all records from the right table appear in results, even without matching records in the left table. This is useful when your focus is on the second table and you want to see all its records regardless of relationships. For example, showing all orders even if some don't have valid customer references due to data issues.

-- Get all orders and customer details (if available)
SELECT 
    c.customer_id,
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

🔹 Finding Orphaned Records

RIGHT JOIN helps identify orphaned records in the right table that lack corresponding entries in the left table. By checking for NULL values in left table columns, you can find data integrity issues like orders without valid customers or transactions without accounts. This is valuable for data cleaning and identifying referential integrity problems in your database.

-- Find orders without valid customer references
SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    o.customer_id AS invalid_customer_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

🔹 RIGHT JOIN vs LEFT JOIN

RIGHT JOIN and LEFT JOIN are functionally equivalent - you can rewrite any RIGHT JOIN as a LEFT JOIN by swapping table positions. Most developers prefer LEFT JOIN because it reads more naturally, with the primary table first. RIGHT JOIN is less common but useful when the query structure makes more sense with a specific table order or when modifying existing queries.

-- These two queries produce the same result:

-- Using RIGHT JOIN
SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- Equivalent LEFT JOIN (preferred)
SELECT c.name, o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

🔹 RIGHT JOIN with Aggregates

Combining RIGHT JOIN with aggregate functions lets you calculate statistics for all records in the right table, including those without matches in the left table. This is useful for comprehensive reports where you need to show all items from the right table with their associated counts or sums, even when some items have zero related records from the left table.

-- Count customers per city (show all cities)
SELECT 
    ci.city_id,
    ci.city_name,
    ci.country,
    COUNT(c.customer_id) AS customer_count
FROM customers c
RIGHT JOIN cities ci ON c.city_id = ci.city_id
GROUP BY ci.city_id, ci.city_name, ci.country
ORDER BY customer_count DESC;

🔹 When to Use RIGHT JOIN

While RIGHT JOIN can always be rewritten as LEFT JOIN, there are specific scenarios where it might be preferred. Use RIGHT JOIN when the query logic naturally focuses on the second table, when modifying existing queries where changing table order is impractical, or when working with views where the table order is predetermined. However, for new queries, LEFT JOIN is generally recommended for better readability.

-- Practical use case: Show all products with optional supplier info
SELECT 
    s.supplier_name,
    s.contact_email,
    p.product_id,
    p.product_name,
    p.price
FROM suppliers s
RIGHT JOIN products p ON s.supplier_id = p.supplier_id
WHERE p.in_stock = 1;