Um JOIN interno descarta as linhas que não têm par. Muitas vezes essa é exatamente a linha sobre a qual perguntam: clientes que nunca fizeram pedido, produtos que nunca foram vendidos. LEFT JOIN mantém todas as linhas da tabela da esquerda (a que vem depois de FROM) e preenche com NULL as colunas do lado direito quando não há correspondência:
SELECT c.first_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;
Os clientes sem pedidos aparecem uma vez, com order_id NULL. Para encontrar só eles, adicione WHERE o.order_id IS NULL. Esse padrão é muito cobrado em entrevistas.
COUNT(*) também conta essa linha com NULL, então um cliente sem pedidos conta como 1; COUNT(o.order_id) dá o valor correto, 0. Uma condição WHERE sobre a tabela da direita, como o.status = 'delivered', remove as linhas com NULL e transforma seu LEFT JOIN de volta em uma junção interna, sem nenhum erro. Coloque condições assim na cláusula ON.
Clientes que nunca fizeram um pedido.