Keeping every row with LEFT JOIN

An inner join drops rows that don't match. A LEFT JOIN keeps every row from the first (left) table, and fills the other table's columns with NULL where there's no match:

SELECT p.name AS product, s.name AS supplier
FROM products p
LEFT JOIN suppliers s ON s.id = p.supplier_id

Every product appears, including the few with no supplier recorded, whose supplier is NULL.

This matters most when counting. To count each customer's orders, including the customers with none, count a column from the right-hand table:

SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name

COUNT(o.id) skips NULLs, so a customer with no orders gets 0. COUNT(*) would give them 1.