SQL RIGHT JOIN: Keep All Rows from the Right Table (Examples)

SQL Tutorial · Chapter 22 of 32

The SQL RIGHT JOIN returns every row from the right (second) table together with the matching rows from the left table. Where a right-hand row has no partner, the left-hand columns are NULL. It is the mirror image of LEFT JOIN, and it is the quickest way to spot “orphan” records such as orders whose customer no longer exists.

SQL RIGHT JOIN Syntax

SELECT columns
FROM left_table
RIGHT JOIN right_table
    ON left_table.key = right_table.key;

RIGHT OUTER JOIN means exactly the same. The table named after RIGHT JOIN is the one whose rows are all kept.

Sample Tables

Order 105 belongs to CustomerID 6, which is not in Customers. That row is what RIGHT JOIN will reveal.

Example 1: All Orders with Customer Names

SELECT c.Name, o.OrderID, o.Product, o.Amount
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
ORDER BY o.OrderID;

All six orders appear. Order 105 has a NULL Name because no customer matches. Emma Brown is absent because she has no order on the right side.

Example 2: Find Orphan Orders

SELECT o.OrderID, o.CustomerID, o.Product
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE c.CustomerID IS NULL;

This is a standard data-quality check: orders that reference a customer that does not exist.

Example 3: The Same Query as a LEFT JOIN

Every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Most developers prefer the LEFT form, so RIGHT JOIN is rare in real code.

SELECT c.Name, o.OrderID, o.Product, o.Amount
FROM Orders o
LEFT JOIN Customers c ON c.CustomerID = o.CustomerID
ORDER BY o.OrderID;

Result: identical to Example 1, six rows with a NULL Name for order 105.

Example 4: RIGHT JOIN with Aggregation

SELECT COALESCE(c.Country, 'Unknown') AS Country, SUM(o.Amount) AS Revenue
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
GROUP BY COALESCE(c.Country, 'Unknown')
ORDER BY Revenue DESC;

The 80 from the orphan order is not lost; it lands in an “Unknown” bucket so totals still reconcile.

RIGHT JOIN vs LEFT JOIN

Works in MySQL, SQL Server, PostgreSQL, SQLite

• MySQL, SQL Server and PostgreSQL fully support RIGHT JOIN.
• SQLite added RIGHT JOIN (and FULL JOIN) in version 3.39 (2022). Older SQLite builds need the LEFT JOIN rewrite.
• Because of that history, LEFT JOIN is the portable choice; reserve RIGHT JOIN for readability when the preserved table naturally comes second.

Reading a RIGHT JOIN Query

When you meet a RIGHT JOIN in existing code, read it from the right: the table after JOIN is complete, the table after FROM is optional. Rewriting it as a LEFT JOIN is a safe refactor that most teams welcome.

Common Mistakes

• Confusing which table is preserved; with RIGHT JOIN it is the second table.
• Filtering the left table in WHERE (WHERE c.Country = 'India'), which discards the NULL rows you were trying to keep. Move the test into ON.
• Mixing LEFT and RIGHT JOINs in one query, which quickly becomes unreadable. Rewrite everything as LEFT JOINs.

Practice Exercise

1. List every order’s Product with the customer’s City, showing ‘Unknown’ where the customer is missing.
2. Rewrite Example 2 as a LEFT JOIN.

-- 1
SELECT o.Product, COALESCE(c.City, 'Unknown') AS City
FROM Customers c RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID;

-- 2
SELECT o.OrderID, o.CustomerID, o.Product
FROM Orders o LEFT JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE c.CustomerID IS NULL;
-- 105, 6, Headset

Related Chapters

• SQL LEFT JOIN – the mirror image
• SQL FULL JOIN – keep unmatched rows from both sides
• SQL Joins Introduction – all join types compared
• SQL from Scratch: full course overview

FAQ

What is the difference between RIGHT JOIN and LEFT JOIN?

LEFT JOIN keeps every row from the first table; RIGHT JOIN keeps every row from the second. Swap the table order and one becomes the other.

Why is RIGHT JOIN used so rarely?

Because any RIGHT JOIN can be written as a LEFT JOIN, and most people find it easier to read queries where the preserved table comes first.

Does SQLite support RIGHT JOIN?

Yes, from version 3.39 onwards. Earlier versions do not, so use a LEFT JOIN with the tables swapped for maximum compatibility.

Chapter 22 of 32 · SQL from Scratch: all 32 chapters