Interview › LEFT JOIN trap
SQL interview: WHERE on the right table killed the LEFT JOIN
WHERE o.status = 'Shipped' turns a LEFT JOIN into an INNER JOIN. NULL is not Shipped.
Prompt. All customers, and their shipped orders if any — including customers who never shipped.
Hint: AND o.status = 'Shipped' belongs in the ON
SELECT c.customer_id, c.email, o.order_id, o.total_amount
FROM Customers c
LEFT JOIN Orders o
ON o.customer_id = c.customer_id
AND o.status = 'Shipped';Put the filter in WHERE and the customers with no shipped orders disappear. That is the trap.
Then write it on a ticket
Monday is free. A colleague asks, you type, we mark it.
Clock in free