10.4 RIGHT JOIN

Level: 10 | Version: v1.1 | Author: Meptrasoft

Overview

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

When no match exists, the left-table columns contain NULL.

LEFT TABLE
     +
RIGHT TABLE
     ↓
RIGHT JOIN
     ↓
ALL RIGHT ROWS
+ MATCHING LEFT ROWS

RIGHT JOIN → Keep everything from the right table.

SQL RIGHT JOIN — Keep All Right Rows — Figure 1: RIGHT JOIN keeps every row from the right table and adds matching data from the left table; unmatched left-side values become NULL.

Practice Tables

This topic reuses the customers and orders tables from 10.2 INNER JOIN.

For the original practice data, every order has a matching customer. Therefore, the basic RIGHT JOIN produces the same rows as the INNER JOIN.

To clearly demonstrate the difference, consider an order whose customer does not exist:

order_id = 11
customer_id = 99

Basic RIGHT JOIN

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

The important rule is:

RIGHT TABLE → ALL ROWS KEPT
LEFT TABLE  → MATCHING ROWS ONLY

If an order has no matching customer, customer_name becomes NULL.

Example with No Match

Suppose the orders table contains:

order_id	customer_id
10	7
11	99

Customer 99 does not exist.

The result is:

customer_name	order_id
Karthik Raj	10
NULL	11

The order is still returned because orders is the right table.

No matching customer → left-side values become NULL.

RIGHT JOIN vs LEFT JOIN

These two joins are mirror images.

LEFT JOIN
→ Keep ALL LEFT rows

RIGHT JOIN
→ Keep ALL RIGHT rows

For example:

FROM customers c
LEFT JOIN orders o ...

keeps all customers.

While:

FROM customers c
RIGHT JOIN orders o ...

keeps all orders.

In practice, many developers prefer LEFT JOIN because reversing the table order can often make the query easier to read.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
RIGHT JOIN keeps all left rows	It keeps all right rows
Unmatched right rows disappear	They remain, with `NULL` on the left
RIGHT JOIN is different from LEFT JOIN in logic	They are mirror-image concepts
The right table is always the physically stored/rightmost database object	“Right” means the table written after `RIGHT JOIN`
RIGHT JOIN always produces more rows than INNER JOIN	Only when unmatched right-side rows exist

Placement Quick Points

RIGHT JOIN
→ ALL RIGHT ROWS

MATCH
→ ADD LEFT DATA

NO MATCH
→ LEFT SIDE = NULL

RIGHT JOIN keeps every row from the right table.
Matching rows from the left table are added.
If no match exists, left-side columns contain NULL.
It is the mirror image of LEFT JOIN.
The difference becomes visible when the right table contains unmatched rows.

Interview Questions

What is a RIGHT JOIN?

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

What happens when there is no match?

The right-side row remains, and the left-table columns contain NULL.

What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN  → Keep all left rows
RIGHT JOIN → Keep all right rows

When does RIGHT JOIN differ from INNER JOIN?

When the right table contains rows that have no matching row in the left table.

Which table is the right table?

The table written immediately after RIGHT JOIN.

Practice & Hands-On Exercises

Using customers and orders:

Write a basic RIGHT JOIN:

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

Display customer names with order IDs.
Explain which table's rows are always preserved.
Add an order with a non-existing customer_id and observe the result.
Compare the result with an INNER JOIN.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

RIGHT JOIN
     ↓
ALL RIGHT ROWS
     +
MATCHING LEFT ROWS
     ↓
NO MATCH → NULL

RIGHT JOIN keeps all rows from the right table and fills the left-side columns with NULL when no match exists.

End of RIGHT JOIN