Interview › Databases & Caching
What is the difference between an INNER JOIN and a LEFT JOIN?
Databases & Caching · Intermediate level
Answer
An INNER JOIN returns only matching rows from both tables. A LEFT JOIN returns every row from the left table and matching right-table rows, with NULLs when no right-side match exists.
Technical explanation
INNER JOIN filters to rows that match on both sides.
LEFT JOIN preserves all left-table rows and fills right-side columns with NULL when no match exists.
Putting right-table filters in WHERE can accidentally turn a LEFT JOIN into an INNER JOIN.
Hands-on example
Find customers with no orders:
SELECT c.id, c.email FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;
Support it with:
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);
Check how well your resume matches the role with our free resume checker— match score, ATS check, and the skills you're missing.
More Databases & Caching interview questions
- What is Amazon RDS, and what does it manage for you versus self-managed databases?
- What database engines does RDS support?
- What is the difference between RDS and Aurora?
- What is Multi-AZ in RDS, and how does automatic failover work?
- How long does an RDS Multi-AZ failover typically take, and what triggers it?
- What is the difference between Multi-AZ and a read replica?
- When would you use a read replica, and can it become a standalone database?
- Can a read replica be in a different region, and why would you do that?