SQL · easy
Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
Asked in Data Analyst interviews, in the SQL round.
Short answer
INNER keeps only matches, LEFT keeps all left rows plus matched right rows, FULL keeps all rows from both sides.
| INNER | LEFT | FULL OUTER | |
|---|---|---|---|
| Matched rows | Kept | Kept | Kept |
| Left rows with no match | Dropped | Kept, right side NULL | Kept, right side NULL |
| Right rows with no match | Dropped | Dropped | Kept, left side NULL |
| Row count vs left table | Same or fewer | Exactly the left count | Same or more |
| Reach for it when | You only want pairs | You are enriching a list | You are reconciling two sets |
How to answer it
Definition
All three combine two tables on a condition. They differ in one thing only: which unmatched rows survive. The table above lays that out side by side.
Example
-- every customer, with their orders where any exist
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;Output
customer_id | name | order_id | amount
------------+-------+----------+--------
101 | Ada | 9001 | 120.00
101 | Ada | 9002 | 80.00
204 | Ben | NULL | NULL <- no orders, kept anywaySwap LEFT for INNER and Ben disappears. That single row is the entire difference, and it is usually the row the question is really about: customers who never ordered, events with no matching session, rows that failed to load.
The filter trap
A WHERE clause on the right-hand table turns a LEFT JOIN back into an INNER one, because a comparison against NULL is not true:
-- silently an INNER JOIN
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 100;
-- what was meant: the condition belongs in the join
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 100;Key points
- The choice is entirely about which unmatched rows you want to keep.
- Any condition on the outer side belongs in
ON, not inWHERE, or the join collapses to INNER. WHERE right.key IS NULLafter a LEFT JOIN is the idiom for "in A but not in B".- A join can multiply rows. If the right side has duplicates on the key, the left row repeats and a later
SUMis wrong. - FULL OUTER is for reconciliation, when either side may hold rows the other lacks.
FAQ
- Is RIGHT JOIN ever needed? It is a LEFT JOIN with the tables swapped. Most teams standardise on LEFT for readability.
- Does join order change the result? For INNER, no. For outer joins, yes: which side is preserved is the whole point.
- How do I find rows in A with no match in B? LEFT JOIN, then
WHERE b.key IS NULL.
The 30-second answer
They all match rows on a condition, and the only difference is what happens to rows that do not match. INNER drops them from both sides. LEFT keeps every row of the left table and fills the right with NULLs. FULL OUTER keeps unmatched rows from both.
In practice I choose based on which unmatched rows I care about: LEFT when I am enriching a list and must not lose anyone, INNER when I only want pairs, FULL OUTER when reconciling two sources. The mistake I watch for is putting a condition on the right-hand table in the WHERE clause after a LEFT JOIN, because NULL fails the comparison and it quietly becomes an INNER join. That condition belongs in the ON clause.
In the interview
- What they are checking: the
WHEREversusONdistinction and the fan-out behavior, more than the definitions. - Common mistake: filtering the right table in
WHEREand wondering where the unmatched rows went.