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, plus the matching rows from the left table. When a right-hand row has no partner, the left-hand columns come back as NULL. It is the mirror image of LEFT JOIN, and it is a quick way to find “orphan” records, such as orders whose customer no longer exists.

This lesson is part of our free SQL series. You will see the syntax, two small sample tables, six worked examples with their exact results, the most common mistakes, and how RIGHT JOIN behaves in MySQL, SQL Server, PostgreSQL and SQLite. If joins are new to you, read the SQL Joins introduction first.

SQL RIGHT JOIN Syntax

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

A simple way to remember it: with RIGHT JOIN, the table on the right of the keyword is the one that is complete.

Sample Tables Used in This Lesson

Customers

Orders

Two details matter. Order 105 belongs to CustomerID 6, which does not exist in Customers. And Emma Brown (CustomerID 5) has never ordered. Watch how each example treats these two rows.

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 missing because she sits in the left table and has no order to match.

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;

Result: one row — 105 | 6 | Headset. Filtering on IS NULL in the left table keeps only right-hand rows that found no partner. This is a standard data-quality check before a migration or a report.

Example 3: The Same Query Written as a LEFT JOIN

Every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Most teams prefer the LEFT form, so you will see RIGHT JOIN less often in production 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. See the SQL LEFT JOIN lesson for more on this form.

Example 4: RIGHT JOIN with GROUP BY and COALESCE

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;

India is 1200 + 300 from Anita’s two orders. The 80 from the orphan order is not lost; it lands in an “Unknown” bucket, so the total (2,800) still matches the Orders table. Australia does not appear because Emma has no orders.

Example 5: Count Orders per Customer, Including Missing Ones

SELECT COALESCE(c.Name, 'Missing customer') AS Customer,
       COUNT(o.OrderID) AS Orders
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
GROUP BY COALESCE(c.Name, 'Missing customer')
ORDER BY Orders DESC, Customer;

Result: Anita Sharma 2, John Smith 1, Maria Garcia 1, Missing customer 1, Wei Chen 1. Every order is counted exactly once, and the orphan is labelled clearly instead of showing a blank name.

Example 6: Filter in ON, Not in WHERE

Suppose you want every order, but customer names only for Indian customers. A common first attempt filters in WHERE:

-- Returns only 2 rows (101 and 103)
SELECT c.Name, o.OrderID
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE c.Country = 'India';

The WHERE clause runs after the join, and NULL = 'India' is never true, so the unmatched rows disappear and the RIGHT JOIN behaves like an INNER JOIN. Move the test into ON instead:

-- Returns all 6 orders
SELECT c.Name, o.OrderID
FROM Customers c
RIGHT JOIN Orders o
  ON o.CustomerID = c.CustomerID
 AND c.Country = 'India';

Now orders 101 and 103 show Anita Sharma, and orders 102, 104, 105 and 106 show NULL for Name. Read more about how NULL behaves in comparisons in SQL NULL Values.

RIGHT JOIN vs LEFT JOIN vs INNER JOIN

The LEFT JOIN count assumes FROM Customers c LEFT JOIN Orders o: five customers, with Anita appearing twice and Emma once with NULL order columns, which gives six rows. To keep unmatched rows from both sides at once, use a SQL FULL JOIN. The SQL INNER JOIN lesson covers the matched-only case.

Database Support

• MySQL, SQL Server, PostgreSQL and Oracle fully support RIGHT JOIN and RIGHT OUTER JOIN.
• SQLite added RIGHT JOIN and FULL JOIN in version 3.39.0 (2022). Older builds stop with an error saying RIGHT and FULL OUTER JOINs are not supported; use the LEFT JOIN rewrite there.
• Because of that history, LEFT JOIN is the most portable choice. Keep RIGHT JOIN for cases where the preserved table naturally comes second in the query.

Common Mistakes and How to Fix Them

• Preserving the wrong table. With RIGHT JOIN it is the second table. If the result is missing rows you expected, check which side is preserved.
• Filtering the left table in WHERE. As Example 6 shows, this silently removes the NULL rows. Put the condition in ON.
• “Ambiguous column name” errors. Both tables have CustomerID. Always prefix columns with a table alias such as o.CustomerID.
• Counting the wrong column. COUNT(*) counts the row even when the left side is NULL; COUNT(c.CustomerID) skips it. Choose deliberately.
• Mixing LEFT and RIGHT JOINs in one query. It quickly becomes hard to read. Rewrite everything as LEFT JOINs.

Tips for Using RIGHT JOIN Well

• Read a RIGHT JOIN from the right: the table after JOIN is complete, the table after FROM is optional.
• Wrap left-table columns in COALESCE() when the output goes into a report, so NULLs show as a clear label.
• Use the WHERE left_key IS NULL pattern as a regular check for orphan records.
• When refactoring old code, converting RIGHT JOINs to LEFT JOINs is a safe change if you swap the table order.

Practice Exercise

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

Answers:

-- 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;
-- returns 105, 6, Headset

Joins and lookups are close cousins of spreadsheet formulas, so the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy is a handy companion if you want to practise the same ideas hands-on.

Frequently Asked Questions

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.

Is RIGHT OUTER JOIN different from RIGHT JOIN?

No. OUTER is optional. RIGHT JOIN and RIGHT OUTER JOIN return exactly the same result.

Why is RIGHT JOIN used so rarely?

Any RIGHT JOIN can be written as a LEFT JOIN, and most people find queries easier to read when the preserved table comes first.

Does SQLite support RIGHT JOIN?

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

Why does my RIGHT JOIN return the same rows as an INNER JOIN?

Usually because a WHERE condition tests a left-table column. NULL never satisfies that test, so unmatched rows are removed. Move the condition into the ON clause.

Wrapping Up

RIGHT JOIN keeps every row of the second table and fills the gaps with NULL. Use it to list all records from one side, to find orphans with IS NULL, and remember that a LEFT JOIN with the tables swapped gives the same answer. Next, try the FULL JOIN lesson, then test yourself with the SQL quiz.

📅 Last updated: October 2026

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