What is the difference between INNER JOIN and LEFT JOIN in SQL?
Quick answer
INNER JOIN returns only rows that have a match in both tables, while LEFT JOIN returns every row from the left table and fills the right table's columns with NULL when there is no match.
Use INNER JOIN when you only want records that exist on both sides, such as orders that have a customer. Use LEFT JOIN when the left table is the main list and the related data is optional, such as all customers including those who have never placed an order.
A common trick is to find rows with no match by left joining and filtering on WHERE right.id IS NULL. Be careful: putting a condition on the right table in the WHERE clause turns a left join back into an inner join, so put it in the ON clause instead. RIGHT JOIN is the mirror image of LEFT JOIN, and FULL OUTER JOIN keeps unmatched rows from both sides.
-- every customer, with order count (0 if none)
SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
-- customers who never ordered
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Key points
- INNER: matches only. LEFT: all left rows plus matches
- Unmatched right-side columns are NULL
- Filter the right table in ON, not WHERE, to keep left-join behaviour
Questions interviewers ask next
- What is a self join?
- What is a CROSS JOIN?