An inner join returns only rows with a match on both sides. A left join returns every row from the left table plus matched columns from the right, with nulls where there is no match. The silent damage comes from using an inner join for a count: customers with zero orders vanish entirely, so your conversion rate looks better than reality and nothing errors.
Why interviewers ask this
Joins are the single most used tool in an analyst's day, and the wrong choice produces a plausible wrong number rather than an error. Interviewers want to hear that you think about which side must be preserved before you write the query, and that you sanity check row counts before and after a join rather than trusting the result.
How to structure your answer
- Define both joins by which rows survive.
- Give a concrete case where an inner join hides zeros.
- Mention checking row counts before and after joining.
- Note that filtering the right table in WHERE turns a left join into an inner join.
Example answer
Inner keeps only matching rows, left keeps everything on the left and fills nulls where the right side has nothing. The reason I care so much is that the wrong one does not throw an error, it just gives you a confident wrong answer. The classic version is counting customers by joining to orders. With an inner join, anyone who never ordered disappears, so if you then compute average orders per customer you have quietly excluded the entire denominator you cared about. There is a related trap I check for in reviews: if you write a left join and then filter on a column from the right table in the WHERE clause, the nulls get filtered out and you have converted it back into an inner join without noticing. The filter needs to go in the ON clause instead. My habit is to count rows on the base table, run the join, and count again. If it grew, I have a fan out. If it shrank, I lost rows I probably wanted.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- Why does filtering the right table in WHERE break a left join?
- How would you find rows that exist in one table but not the other?
- What is a full outer join useful for in practice?
Related data analyst questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions