3 customers. LEFT JOIN gave back 4 rows. Why?
Because Asha has 2 orders. One-to-many joins repeat the left row. That is not a bug, and the interviewer wants you to say it.
Inside: INNER vs LEFT, the WHERE filter that quietly turns your LEFT JOIN into an INNER JOIN, and how to count orders per customer with zero included.
Save this for your next SQL round.
customers LEFT JOIN orders
| name | order_id | amount |
|---|---|---|
| Asha | 101 | 500 |
| Asha | 102 | 300 |
| Ravi | 103 | 700 |
| Neha | NULL | NULL |
Asha has 2 orders, so she shows up twice. Neha has none, so LEFT JOIN keeps her with NULLs.
INNER vs LEFT
| INNER JOIN | LEFT JOIN | |
|---|---|---|
| Keeps | Matches only | Every left row |
| No match | Row dropped | NULLs on the right |
| Neha | Gone | Kept |
| Rows here | 3 | 4 |
Row count grows when the key repeats on the right side. That is one-to-many, not a bug.
The trap: WHERE on the right table
looks_like_left.sql
-- Neha vanishes: acts like INNER
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
ON o.cust_id = c.id
WHERE o.amount > 400;Neha's amount is NULL. NULL > 400 is not true, so WHERE throws her out.
The fix: move it to ON
real_left_join.sql
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
ON o.cust_id = c.id
AND o.amount > 400;Result: Asha 500, Ravi 700, Neha NULL. Every customer stays.
Orders per customer, zero included
orders_per_customer.sql
SELECT c.id, c.name,
COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o
ON o.cust_id = c.id
GROUP BY c.id, c.name;COUNT(*) gives Neha 1. COUNT(o.order_id) skips NULLs and gives 0.
Which JOIN when
| You need | Write |
|---|---|
| Only customers who ordered | INNER JOIN |
| All customers, orders or not | LEFT JOIN |
| Customers who never ordered | LEFT JOIN + WHERE o.order_id IS NULL |
| Rows went up after a join | Check if the key repeats |
Say this in the interview
Wrong
- Filter the right table in WHERE
- COUNT(*) after a LEFT JOIN
- Expect row count to stay the same
Right
- Put that filter in ON
- COUNT a column from the right table
- Check keys for one-to-many first