SQL RIGHT JOIN
RIGHT JOIN keeps every row of the right (second) table. Left-hand columns become NULL where there is no match.
| id | name |
|---|---|
| 1 | Ada |
| 2 | Grace |
| 3 | Linus |
| 4 | Hedy |
| id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 2 |
| 103 | 1 |
| 108 | 9 |
SELECT c.name, o.id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;Every order, matched or not. Order 108 appears with a NULL name. Most people rewrite this as a LEFT JOIN with the tables swapped.
| name | order id | total |
|---|---|---|
| Ada | 101 | 131 |
| Grace | 102 | 34.5 |
| Ada | 103 | 9.5 |
| NULL | 108 | 42 |
RIGHT JOIN example, row by row
Every order is kept. Order 108 belongs to customer 9, who does not exist, so its name is NULL. Customers without orders are dropped.
SELECT c.name, o.id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;▶ Run it on the full sample database
What to know about RIGHT JOIN
- RIGHT JOIN and RIGHT OUTER JOIN are identical.
- Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order; most style guides prefer LEFT JOIN for readability.
- SQLite supports RIGHT JOIN from version 3.39 (2022); older SQLite builds reject it.
Other join types
- LEFT JOIN — all left rows + matching right rows (null when unmatched)
- INNER JOIN — only rows that match in both tables
- FULL OUTER JOIN — every row from both tables
- All SQL joins compared, including anti-joins, CROSS JOIN and self joins
Syntax for everything else, from GROUP BY to window functions, is on the SQL cheat sheet.
RIGHT JOIN questions
What is a RIGHT JOIN in SQL?
A RIGHT JOIN returns all rows from the right (second) table and the matching rows from the left table, with NULL in the left-hand columns where no match exists.
What is the difference between a LEFT JOIN and a RIGHT JOIN?
Only which side is preserved. a LEFT JOIN b keeps all of a; a RIGHT JOIN b keeps all of b. Swapping the tables turns one into the other.
When should I use a RIGHT JOIN?
Rarely. It is useful when you add a table to the end of a long chain of joins and want to keep all of that table’s rows, but rewriting the query with LEFT JOIN is usually clearer.
Does MySQL support RIGHT JOIN?
Yes. MySQL, PostgreSQL, SQL Server, Oracle and SQLite 3.39+ all support RIGHT JOIN. MySQL lacks FULL OUTER JOIN, not RIGHT JOIN.