SQL · easy

Explain the difference between INNER, LEFT, and FULL OUTER JOIN.

Asked in Data Analyst interviews, in the SQL round.

Short answer

INNER = matches only; LEFT = all left + matched right; FULL = all rows both sides.

How to answer it

Say what each one keeps.

• INNER JOIN: only rows with a match on both sides.
• LEFT JOIN: every row from the left table; right-side columns are NULL where there is no match.
• FULL OUTER JOIN: every row from both tables; NULLs on whichever side is missing.

Then show you know when each one is the right choice:

-- customers who have never ordered: LEFT JOIN, then keep the NULL side
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

The follow-up interviewers like: put a condition on the right table in WHERE and a LEFT JOIN turns back into an INNER JOIN, because the NULL rows fail the filter. Conditions that should preserve unmatched rows belong in the ON clause. Also mention that a join on a non-unique key fans rows out, so COUNT(*) after a join counts matches, not customers.

• What they are checking: the WHERE versus ON distinction and the fan-out behaviour, more than the definitions.
• Common mistake: filtering the right table in WHERE and wondering where the unmatched rows went.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.
• Find users who logged in 3 or more consecutive days.

Practice this for real

• Check your resume against a Data Analyst job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card