LEFT JOIN vs RIGHT JOIN

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

departments

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.

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;

Related SQL Notes

• LEFT JOIN
• RIGHT JOIN
• INNER JOIN vs LEFT JOIN