MySQL RIGHT JOIN: How It Works

RIGHT JOIN is the mirror image of LEFT JOIN: it keeps every row of the right table, and fills the left-hand columns with NULL when there is no match.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

query.sql

SQL

SELECT c.name, o.id AS order_id, o.total
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id
ORDER BY c.id, o.id;

Output

OUTPUT

+-------------+----------+---------+
| name        | order_id | total   |
+-------------+----------+---------+
| Maya Shah   |        1 |  680.00 |
| Maya Shah   |        2 | 1299.00 |
| Rohan Verma |        3 |  145.00 |
| Rohan Verma |        7 |  220.00 |
| Sana Ali    |        4 |  450.00 |
| Sana Ali    |        5 |   80.00 |
| Sana Ali    |        6 |  999.00 |
| Tara Nair   |     NULL |    NULL |
| Uday Singh  |     NULL |    NULL |
+-------------+----------+---------+
9 rows in set

All five customers are in the result (the right table here), including Tara and Uday, with NULL order values.

It is the same as a LEFT JOIN with the tables swapped

query.sql

SQL

SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Output

OUTPUT

+-------------+----------+---------+
| name        | order_id | total   |
+-------------+----------+---------+
| Maya Shah   |        1 |  680.00 |
| Maya Shah   |        2 | 1299.00 |
| Rohan Verma |        3 |  145.00 |
| Rohan Verma |        7 |  220.00 |
| Sana Ali    |        4 |  450.00 |
| Sana Ali    |        5 |   80.00 |
| Sana Ali    |        6 |  999.00 |
| Tara Nair   |     NULL |    NULL |
| Uday Singh  |     NULL |    NULL |
+-------------+----------+---------+
9 rows in set

The two queries return the same rows. Every RIGHT JOIN can be written as a LEFT JOIN by swapping the order of the tables, and the reverse.

Why most people only use LEFT JOIN

Reading order. In a LEFT JOIN, the “main” table comes first, and the optional ones follow. A query with several RIGHT JOINs makes the reader jump back and forth.
Consistency. A team that uses only LEFT JOIN for outer joins never has to ask which side is kept.
Portability. All databases support both, but LEFT is far more common in examples and tools. Some ORMs only generate LEFT JOIN.

There is no performance reason either way: MySQL internally converts a RIGHT JOIN into a LEFT JOIN.

Anti-join with RIGHT JOIN

The “no match” pattern works the same way:

query.sql

SQL

SELECT c.id, c.name
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id
WHERE o.id IS NULL
ORDER BY c.id;

Output

OUTPUT

+----+------------+
| id | name       |
+----+------------+
|  4 | Tara Nair  |
|  5 | Uday Singh |
+----+------------+
2 rows in set

A case where RIGHT JOIN reads naturally

When you already have a long query and want to add one more table whose rows must all be kept, a RIGHT JOIN at the end keeps the earlier text unchanged. Even then, a common style rule is to rewrite the query so that the table to keep comes first.

The mnemonic

> A LEFT JOIN B keeps all of A. A RIGHT JOIN B keeps all of B.

If you can say it that way, you can use either.

lightbulb

House rule

Use LEFT JOIN everywhere. If you meet a RIGHT JOIN in someone else’s query, swap the tables and change it to LEFT JOIN in your head (or in the query), and it will read normally.