SQL RIGHT JOIN: Keeping All Rows from the Right Table

Lesson 5.4 · Reading time: ~4 min

The SQL RIGHT JOIN returns every row of the right table and the matching rows of the left table; where there is no match, the columns of the left table are NULL. In this lesson you'll learn the RIGHT JOIN syntax, how it mirrors LEFT JOIN, and why most developers rewrite it as a LEFT JOIN.

RIGHT JOIN is the mirror image of LEFT JOIN. The difference is only which table keeps all its rows: with LEFT JOIN it is the table after FROM, with RIGHT JOIN it is the table after JOIN.

What RIGHT JOIN Returns

• Every row of the right table (the one after RIGHT JOIN) appears in the result.
• Rows with matches are combined with them, as in INNER JOIN.
• Rows without a match get NULL in all columns of the left table.

RIGHT JOIN and RIGHT OUTER JOIN are the same.

Basic Syntax

SELECT left_table.column1, right_table.column2
FROM left_table
RIGHT JOIN right_table ON left_table.key_column = right_table.key_column;

Example: All Addresses and Their Staff

The address table stores the addresses of customers, staff and stores. To list all addresses and show which of them belong to staff members:

SELECT s.first_name, s.last_name, a.address, a.district
FROM staff AS s
RIGHT JOIN address AS a ON s.address_id = a.address_id
ORDER BY s.staff_id IS NULL, a.address
LIMIT 10;

Result: all 603 addresses; the two addresses of staff members come first with their names, the rest have NULL in first_name and last_name.

RIGHT JOIN as a LEFT JOIN

Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables. These two queries return the same rows:

-- RIGHT JOIN: all addresses are kept
SELECT s.first_name, s.last_name, a.address
FROM staff AS s
RIGHT JOIN address AS a ON s.address_id = a.address_id;

-- LEFT JOIN with the tables swapped: the same result
SELECT s.first_name, s.last_name, a.address
FROM address AS a
LEFT JOIN staff AS s ON s.address_id = a.address_id;

Result: both queries return 603 rows.

Most developers use only LEFT JOIN. The query then reads naturally: the main table comes first after FROM, and every following join only adds information to it. Mixing LEFT and RIGHT in one query makes it hard to see which rows are guaranteed to stay.

When RIGHT JOIN Is Useful

• Reading existing code. You'll meet RIGHT JOIN in other people's queries and in queries generated by tools.
• Adding a table to the end of a long query. Sometimes it is simpler to append RIGHT JOIN than to reorder a long chain of joins. Even then, rewriting the query with LEFT JOIN usually makes it clearer.

SQLite added RIGHT JOIN only in version 3.39 (2022); in older versions, use LEFT JOIN with the tables swapped.

Key Takeaways:

• RIGHT JOIN keeps every row of the right table; unmatched rows get NULL in the left table's columns.
• A RIGHT JOIN B returns the same rows as B LEFT JOIN A.
• Most queries use LEFT JOIN with the main table after FROM: it is easier to read.
• Avoid mixing LEFT JOIN and RIGHT JOIN in one query.

Frequently Asked Questions

What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN keeps all rows of the table after FROM; RIGHT JOIN keeps all rows of the table after JOIN. Swapping the tables turns one into the other.

Why do developers rarely use RIGHT JOIN?

Because a LEFT JOIN with the tables swapped gives the same result and reads more naturally: the main table comes first, and the joined tables add details to it.

Is RIGHT JOIN the same as RIGHT OUTER JOIN?

Yes. The keyword OUTER is optional.