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.
The Core Difference
They're mirror images of each other — the only difference is which table's unmatched rows are preserved.
| LEFT JOIN | RIGHT JOIN | |
|---|---|---|
| Keeps all rows from | The table listed first (before FROM's join keyword) | The table listed second (after the join keyword) |
| Unmatched rows shown from | Left table | Right table |
Proving They're Equivalent
-- These two queries return identical results:
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
SELECT e.name, d.department_name
FROM departments d
RIGHT JOIN employees e ON e.department_id = d.id;Swap the table order in FROM, and a RIGHT JOIN becomes a LEFT JOIN with the same result. Neither is more "correct" — but LEFT JOIN is used far more often in real-world code, mostly for readability: it reads naturally as "start from this table, then bring in related data."
Why Most Style Guides Prefer LEFT JOIN
- It's supported identically everywhere (Oracle historically had quirks with RIGHT JOIN)
- Reading top-to-bottom, "FROM employees LEFT JOIN departments" reads as "employees is the anchor" — more intuitive than parsing a RIGHT JOIN's direction
- Consistency — if a codebase always uses LEFT JOIN, developers don't have to context-switch between two mental models
Common Mistake
Mixing LEFT and RIGHT JOINs within the same multi-table query without a clear reason — it makes the query much harder to trace mentally. Pick one convention (usually LEFT JOIN) and restructure the table order instead of switching join direction.
Interview Relevance
Interviewers sometimes ask you to rewrite a RIGHT JOIN as a LEFT JOIN (or vice versa) specifically to confirm you understand they're logically equivalent, not two unrelated concepts.
Practice Question
Rewrite this RIGHT JOIN as an equivalent LEFT JOIN: SELECT o.id, c.name FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id;