LEFT JOIN: Keeping the Unmatched

The customer who never ordered

Meet Dan from Oslo, customer id 4, zero orders. Ask "show each customer with their orders" using the INNER JOIN from unit 5 and Dan simply vanishes: he has no matching order row, so he produces no output.

Often that is wrong. "All customers and their orders" should include the quiet ones. That is LEFT JOIN:

SELECT customers.name, orders.item
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;

LEFT JOIN keeps every row of the left table (the one named before the join, here customers). When a row finds matches it joins normally. When it finds none, it still emits one row, with every column from the right table set to NULL. In the sandbox output, that NULL is the empty spot after the last |.

The find-the-missing pattern

LEFT JOIN plus IS NULL (from lesson 2-3) answers "which rows have NO partner?":

SELECT customers.name
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
WHERE orders.id IS NULL;

Every matched customer has a real orders.id in their joined row. Only the unmatched ones carry NULL there. Filter on it and you get exactly the customers who never ordered. Memorize this shape, it comes up constantly: users who never logged in, products never sold, students in no course.