What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table, and fills the right table's columns with NULL when there is no match.
Explain it simply
Picture two lists in a college: students, and library books they have borrowed. An INNER JOIN is like asking "show me students who borrowed a book, with the book". Students with no books disappear. A LEFT JOIN is like asking "show me every student, and their book if they have one". Students with no books still appear, with an empty book column (NULL).
Example
-- students(id, name) borrowings(student_id, book)
SELECT s.name, b.book
FROM students s
INNER JOIN borrowings b ON b.student_id = s.id;
-- Only students who borrowed something
SELECT s.name, b.book
FROM students s
LEFT JOIN borrowings b ON b.student_id = s.id;
-- Every student; book is NULL when they borrowed nothing
-- Find students who never borrowed a book
SELECT s.name
FROM students s
LEFT JOIN borrowings b ON b.student_id = s.id
WHERE b.student_id IS NULL;How it works
The database compares rows from both tables using the ON condition. For INNER JOIN it keeps only combinations where the condition is true. For LEFT JOIN it keeps those combinations too, and for any left row that found nothing it adds one row with NULLs for the right side. If a student borrowed three books, the student appears three times in both joins, once per match.
Real-world use
- INNER JOIN: orders with their customer details, where every order must have a customer.
- LEFT JOIN: reports that must list everyone, such as "all products and their sales this month, including products with zero sales".
- LEFT JOIN with
IS NULL: finding missing data, such as users who never logged in.
Common mistakes
- Putting a condition on the right table in the WHERE clause after a LEFT JOIN (for example
WHERE b.book = 'SQL'). It removes the NULL rows and quietly turns it into an inner join. Put such conditions in the ON clause instead. - Forgetting that one-to-many joins multiply rows, which breaks SUM and COUNT. Aggregate first or use
COUNT(DISTINCT ...). - Mixing up which table is "left": it is the one written first, before
LEFT JOIN.
What to say in the interview
"An inner join returns only rows that match in both tables. A left join returns all rows from the left table plus matching rows from the right, with NULLs where there is no match. I use left joins for reports that must include everything, and a left join with WHERE right.id IS NULL to find missing records. I am careful not to filter the right table in WHERE after a left join, because that turns it into an inner join."
Follow-up questions
- What are RIGHT JOIN and FULL OUTER JOIN?
- How is a join different from a subquery with IN or EXISTS?
- Why might a join return more rows than you expected?