RIGHT JOIN (or RIGHT OUTER JOIN) returns every row from the right table, plus matching data from the left table — the mirror image of LEFT JOIN.
🔵 Shaded = all of departments (right table) is kept, matched or not.
Reference Tables Used in This Section
Every join note below uses these same two tables, so you can see exactly how each join type treats the same data differently.
employees
| id | name | department_id |
|---|---|---|
| 1 | Aman | 1 |
| 2 | Riya | 2 |
| 3 | Karan | 1 |
| 4 | Neha | NULL |
departments
| id | department_name |
|---|---|
| 1 | Engineering |
| 2 | Marketing |
| 3 | Sales |
Notice: Neha has no department (department_id is NULL), and Sales has no employees at all. These two "unmatched" rows are what make the differences between join types visible.
Syntax
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;Example
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;Expected Output:
| name | department_name |
|---|---|
| Aman | Engineering |
| Karan | Engineering |
| Riya | Marketing |
| NULL | Sales |
All 3 departments appear — including Sales, which has no employees, shown with NULL in name. Neha (no department) is excluded, since she's on the unkept left side.
Dialect Note
MySQL, PostgreSQL, and SQL Server all support RIGHT JOIN. Oracle historically didn't support the keyword the same way in older versions (used (+) operator syntax instead) — modern Oracle supports standard RIGHT JOIN too.
Practical Use Case
Same logic as LEFT JOIN, just from the other side — useful when it reads more naturally to lead with the table you want all rows from. In practice, most SQL style guides prefer rewriting a RIGHT JOIN as a LEFT JOIN with the tables swapped in FROM, since it's more consistently supported and easier to scan when reading top-to-bottom.
Common Mistakes
- Using RIGHT JOIN out of habit from how the tables happen to be listed, instead of just swapping table order and using LEFT JOIN — functionally identical, but LEFT JOIN is far more common in real codebases, so it's easier for teammates to read
- Assuming RIGHT JOIN behaves differently from a swapped LEFT JOIN — it doesn't; they're mirror images
Interview Relevance
Q: "Can every RIGHT JOIN be rewritten as a LEFT JOIN?" Yes — swap the table order in FROM/JOIN and change the keyword; the result is identical. Interviewers ask this to see if you understand joins conceptually rather than as memorized keyword patterns.
Practice Question
Rewrite the RIGHT JOIN example above as an equivalent LEFT JOIN query.