What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?
sql-jun-001
Your answer
Answer as you would in a real interview — explain your thinking, not just the conclusion.
Model answer
INNER JOIN returns only the rows where there is a match in both tables — non-matching rows on either side are excluded. LEFT JOIN (LEFT OUTER JOIN) returns all rows from the left table plus matching rows from the right; where there is no match the right-side columns come back as NULL. RIGHT JOIN is the mirror image: all rows from the right table are kept and left-side columns are NULL when there is no match. In practice I almost always use LEFT JOIN over RIGHT JOIN because it reads more naturally left-to-right. I think of INNER JOIN as the intersection and LEFT JOIN as 'give me everything from the main table, optionally enriched by the secondary table'.
Code example
-- INNER JOIN: only matched rows
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
-- LEFT JOIN: all orders, customer name NULL if no match
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;
-- Find orders with no customer (orphaned rows)
SELECT o.id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;Follow-up
What is a FULL OUTER JOIN? Give an example where you would need it instead of a LEFT JOIN.