Data Analyst interview question
What is the difference between LEFT JOIN and INNER JOIN?
INNER JOIN returns only rows with a match on both sides; LEFT JOIN keeps every left-side row and fills unmatched right-side columns with NULL. Choose based on whether missing relationships must remain visible.
Direct answer
What a strong answer covers
How it works
The join type decides what happens to rows without a partner. INNER JOIN silently drops them, which is correct when the relationship is mandatory and wrong when its absence is exactly the thing being measured. LEFT JOIN preserves every row from the first table and produces NULL for the missing side, so counts and sums must use the right-side columns to avoid counting the placeholder row. A filter on the right-side table in WHERE turns a LEFT JOIN back into an INNER JOIN; that condition belongs in the ON clause. Duplicate matches multiply rows in both join types.
Example
Counting customers without orders requires customers LEFT JOIN orders and WHERE orders.id IS NULL; with an INNER JOIN those customers never appear. A revenue-per-customer report that used LEFT JOIN and COUNT(*) reported one order for customers who had none, because COUNT(*) counted the NULL-filled row; COUNT(orders.id) fixed it.
Common mistakes
- Placing a condition on the right-hand table in WHERE and losing the unmatched rows.
- Using COUNT(*) after a LEFT JOIN and counting placeholder rows.
- Joining on a non-unique key and doubling the metric without noticing.
A useful answer structure
Answer from your own work.
Do not memorise a script: use only examples you can explain honestly in follow-up questions.
Open the desktop workspace and rehearse a clear answer in your own words.