RIGHT JOIN in MySQL

Introduction

RIGHT JOIN helps you combine two tables and keep all records from the table on the right side, even if there is no match in the left table.

You want to list all customers and their orders, including customers who have no orders.

You want to see all products and their sales, even if some products have no sales yet.

You want to compare two lists and keep all items from the second list, showing matching info from the first list if available.

Syntax

MySQL

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

RIGHT JOIN returns all rows from the right table and matched rows from the left table.

If there is no match, columns from the left table will be NULL.

Examples

Shows all departments and their employees. Departments without employees still appear.

MySQL

SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

Lists all customers and their orders. Customers without orders still appear.

MySQL

SELECT orders.id, customers.name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.id;

Sample Program

This query shows all orders and the customer who made them. If an order had no customer, it would still appear (but here all orders have customers).

MySQL

CREATE TABLE customers (
  id INT,
  name VARCHAR(20)
);

CREATE TABLE orders (
  id INT,
  customer_id INT,
  product VARCHAR(20)
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES (101, 1, 'Book'), (102, 1, 'Pen'), (103, 3, 'Notebook');

SELECT customers.name, orders.product
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id
ORDER BY orders.id;

Important Notes

RIGHT JOIN is like LEFT JOIN but keeps all rows from the right table instead of the left.

If you want all rows from the left table, use LEFT JOIN instead.

Use ORDER BY to organize your results for easier reading.

Summary

RIGHT JOIN keeps all rows from the right table and matches from the left.

Unmatched right table rows show NULL values.

Useful to see all data from the right table with related info from the left.