What is the difference between LEFT JOIN and RIGHT JOIN?
LEFT JOIN keeps every row of the left table and fills NULLs where the right has no match. RIGHT JOIN does the opposite. They are mirror images, and any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables.
Explain it simply
Take the class list and the exam-fee receipts. "All students, with receipt if they paid" is a LEFT JOIN from students. "All receipts, with student if known" is a RIGHT JOIN from students. The only difference is which table you promise to keep completely.
Example
-- all departments, even those with no employees
SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id;
-- same result, written as a RIGHT JOIN
SELECT d.name, e.name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;Both queries keep every department. An empty department shows NULL under the employee name.
Most teams write only LEFT JOINs, because reading tables from left to right, with the "main" table first, is easier. Swapping the table order turns one into the other.
What to say in the interview
"LEFT JOIN preserves all rows from the left table, RIGHT JOIN from the right. They are symmetric. I prefer LEFT JOIN consistently for readability, and I know that the unmatched side shows up as NULLs."
Common mistakes
- Thinking RIGHT JOIN does something LEFT JOIN cannot. It does not.