SQL RIGHT JOIN

RIGHT JOIN is LEFT JOIN flipped: it keeps every row from the right table (the one named after JOIN) instead of the left one. In practice it's written far less often than LEFT JOIN, since any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping which table comes first, most teams just pick LEFT JOIN as a convention and stick to it.

Learning Objectives

Write a RIGHT JOIN and know which table's rows are guaranteed to appear.
Rewrite any RIGHT JOIN as an equivalent LEFT JOIN.
Know which database in this course doesn't support RIGHT JOIN at all.

employees (left)

Matched where possible

RIGHT JOIN

Keeps every right row

departments (right)

All 5 rows, including Legal

Result

Same result as the LEFT JOIN version, flipped

Basic RIGHT JOIN

departments is now on the right (named after RIGHT JOIN), so every department row is guaranteed, Legal appears with first_name as NULL, exactly the same actual result as the LEFT JOIN version from the previous lesson, with the FROM/JOIN order swapped to match.

MySQL / MariaDB / PostgreSQL / SQL Server / Oracle

SELECT e.first_name, d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Rewriting RIGHT JOIN as LEFT JOIN

Swap which table is named first, change RIGHT JOIN to LEFT JOIN, and the result is identical. This is exactly why many style guides skip RIGHT JOIN entirely, one join keyword to remember instead of two, at the cost of sometimes reordering FROM and JOIN.

These two produce identical results

-- RIGHT JOIN version
SELECT e.first_name, d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- Equivalent LEFT JOIN version
SELECT e.first_name, d.name AS department_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;

SQLite Doesn't Support RIGHT JOIN

SQLite is the one database in this course without a RIGHT JOIN keyword at all, it only supports LEFT JOIN (and, from version 3.39 onward, FULL JOIN). The rewrite from the previous section isn't just a style choice on SQLite, it's required.

Database	RIGHT JOIN Support
MySQL / MariaDB / PostgreSQL / SQL Server / Oracle	Supported
SQLite	Not supported, rewrite as LEFT JOIN instead

NOTE

This is a real, practical reason RIGHT JOIN sees so little everyday use: if there's any chance a query needs to run on SQLite, LEFT JOIN is the only portable option.

Try It Yourself

Write the Legal-department query using RIGHT JOIN, then rewrite it as an equivalent LEFT JOIN.

Answer — RIGHT JOIN

SELECT e.first_name, d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

Answer — equivalent LEFT JOIN

SELECT e.first_name, d.name AS department_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;

Frequently Asked Questions

Is there ever a real reason to prefer RIGHT JOIN over rewriting as LEFT JOIN?

Occasionally, when a long query already reads more naturally with a specific table named first in FROM, and adding RIGHT JOIN avoids reordering everything. It's a minor readability call, never a functional one, LEFT JOIN can always express the same result.

Does RIGHT JOIN have an OUTER keyword too?

Yes, RIGHT OUTER JOIN means exactly the same as RIGHT JOIN, OUTER is optional, same as with LEFT JOIN.

Common Beginner Mistakes

Using RIGHT JOIN on SQLite

It simply isn't supported, SQLite raises a syntax error. Rewrite as LEFT JOIN with the table order swapped instead.

Assuming RIGHT JOIN and LEFT JOIN return different data in general

They return the same shape of result for the same relationship, which table is "kept entirely" is just a matter of which one is named left vs right of the JOIN keyword, not a difference in capability.

Best Practices

Default to LEFT JOIN as a team convention, rewriting any RIGHT JOIN, for consistency and SQLite portability.
If RIGHT JOIN is used, know it instantly rewrites to LEFT JOIN by swapping table order, so it's never actually required.
Check SQLite support specifically before relying on RIGHT JOIN in any portable query.

Interview Questions

What's the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN guarantees every row from the table named before the JOIN keyword; RIGHT JOIN guarantees every row from the table named after it. Any RIGHT JOIN can be rewritten as an equivalent LEFT JOIN by swapping the table order, they're not functionally different capabilities, just a different way of naming which side is kept.

Why would a query written with RIGHT JOIN fail on SQLite specifically?

SQLite doesn't implement the RIGHT JOIN keyword at all (pre-3.39, it also lacks FULL JOIN), only LEFT JOIN and INNER JOIN. The fix is rewriting the RIGHT JOIN as an equivalent LEFT JOIN with the table order swapped.

Summary

RIGHT JOIN keeps every row from the right-hand table, the mirror of LEFT JOIN, and any RIGHT JOIN can be rewritten as an equivalent LEFT JOIN by swapping table order, required on SQLite since it has no RIGHT JOIN keyword at all.

What's Next?

FULL JOIN next, which keeps unmatched rows from both sides at once, and a look at how to simulate it on the databases that don't support it directly.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.