• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL RIGHT JOIN

← Previous Next →

In relational databases, there are situations where you want to ensure that all records from a specific table appear in your query result, even if there are no corresponding records in another table.

While LEFT JOIN ensures all rows from the left table are included, the RIGHT JOIN ensures all rows from the right table appear. This is particularly useful when you want to display all records from the secondary table, regardless of whether they match the primary table.

What Is a RIGHT JOIN in MySQL?

A RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and only the matching rows from the left table.

  • If a row in the right table doesn’t have a match in the left table, the left table’s columns will contain NULL.

  • If a row exists in both tables, it will appear in the result as usual.

In essence, RIGHT JOIN is the mirror image of LEFT JOIN, just focusing on the right table instead of the left.

RIGHT JOIN Syntax

The basic syntax of a RIGHT JOIN is:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Explanation

  • table1 → The left table (only matching rows appear in result; unmatched rows show NULL).

  • table2 → The right table (all rows are included).

  • ON → The join condition linking the two tables.

Example Tables

Consider two tables, customers and orders.

customers

customer_id customer_name city
1 Ananya Delhi
2 Priya Mumbai
3 Isha Pune

orders

order_id customer_id amount
101 1 500
102 2 700
103 4 300

Notice that customer_id 4 exists in orders but not in customers.

RIGHT JOIN Example

Let’s write a query to display all orders and their corresponding customer names.

SELECT customers.customer_name, orders.order_id, orders.amount
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

Result:

customer_name order_id amount
Ananya 101 500
Priya 102 700
NULL 103 300

Explanation

  • All orders are included because orders is the right table.

  • Order 103 doesn’t have a matching customer record, so customer_name shows NULL.

Using Table Aliases

Table aliases make queries more readable, especially with multiple joins:

SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
RIGHT JOIN orders AS o
ON c.customer_id = o.customer_id;

Aliases (c for customers, o for orders) simplify the query and prevent ambiguity.

RIGHT JOIN with WHERE Clause

You can filter the result using a WHERE clause. For example, to show orders with an amount greater than 500:

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.amount > 500;

Result:

customer_name order_id amount
Priya 102 700

Note:

Rows from the right table still appear in the query, but the WHERE clause filters the final result.

RIGHT JOIN with Multiple Tables

RIGHT JOIN can also be used with multiple tables. Let’s introduce payments:

payments

payment_id order_id payment_status
501 101 Paid
502 103 Pending

To display all orders with customer names and payment status:

SELECT c.customer_name, o.order_id, p.payment_status
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
RIGHT JOIN payments p ON o.order_id = p.order_id;

Result:

customer_name order_id payment_status
Ananya 101 Paid
NULL 103 Pending

  • All orders from the right tables (orders and payments) are included.

  • Missing customer names are displayed as NULL.

RIGHT JOIN vs LEFT JOIN

Feature LEFT JOIN RIGHT JOIN
Ensures all rows from left table appear ✅ ❌
Ensures all rows from right table appear ❌ ✅
Typical use case When main table is left When main table is right
Behavior with unmatched rows Left table rows appear with NULLs for right Right table rows appear with NULLs for left

Tip: You can often rewrite a RIGHT JOIN as a LEFT JOIN by switching the table order. LEFT JOIN is more commonly used in practice, but RIGHT JOIN is useful when you want to focus on the right table.

RIGHT JOIN with Aggregate Functions

You can summarize data while using RIGHT JOIN. For instance, to get total order amount for each order in the orders table:

SELECT o.order_id, SUM(o.amount) AS total_amount
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY o.order_id;

Result:

order_id total_amount
101 500
102 700
103 300

Even orders without matching customers are included in the aggregation.

Performance Considerations

  • Index the columns used in the ON clause to speed up joins.

  • Avoid unnecessary RIGHT JOINs when LEFT JOIN can achieve the same result.

  • Use EXPLAIN to analyze query performance for large tables.

  • Specify only needed columns instead of SELECT * to reduce memory usage.

Common Mistakes to Avoid

  1. Switching LEFT and RIGHT accidentally:
    RIGHT JOIN might not return the expected result if you confuse the left and right table.

  2. Filtering on right table columns in WHERE clause:
    Like LEFT JOIN, filtering on columns from the right table can remove unmatched rows unintentionally.

  3. Joining unrelated columns:
    Always make sure your join condition uses related columns (primary key to foreign key).

When to Use RIGHT JOIN

RIGHT JOIN is useful when:

  • You want to include all rows from a specific table (the right table), even if no matching data exists.

  • You need to generate reports that focus on the secondary table’s completeness.

  • You’re working with queries where switching the table order simplifies logic.

Summary of the Tutorial

The MySQL RIGHT JOIN allows you to retrieve all records from the right table while including matching rows from the left table. If there’s no match, MySQL fills the left table columns with NULL.

While RIGHT JOIN is less commonly used than LEFT JOIN, it’s an essential tool for cases where your primary focus is on the right table. Mastering RIGHT JOIN, along with LEFT and INNER JOIN, gives you full control over how to combine tables in relational databases.

Practice Questions

  1. Write a query to display all orders and their corresponding customer names using RIGHT JOIN, including orders without a matching customer.

  2. Using RIGHT JOIN, list all payments and the associated order IDs and customer names. Show payments even if the order or customer doesn’t exist.

  3. Write a query to show all employees and the projects they are assigned to using RIGHT JOIN, including projects with no assigned employees.

  4. Using RIGHT JOIN, display all products and their supplier names, including products that have no supplier.

  5. Write a query that lists all orders along with the shipping information using RIGHT JOIN, even if some orders haven’t been shipped.

  6. Create a RIGHT JOIN query to display all courses and the students enrolled in them. Include courses with zero students.

  7. Using RIGHT JOIN, list all departments and the employees working in them. Include departments with no employees.

  8. Write a query that shows all orders and the total payment received for each, including orders with no payments.

  9. Using RIGHT JOIN, display all teachers and the subjects they are assigned to, including subjects that have no teachers.

  10. Write a query to list all movies and their ratings using RIGHT JOIN, showing movies even if they haven’t received any rating yet.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top