LEFT JOIN: manter a tabela da esquerda

O problema do INNER JOIN

O INNER JOIN descarta as linhas sem par. Se uma categoria não tem nenhum produto, ela não aparece. Se um produto não tem categoria atribuída (categoria_id é NULL), também não. Às vezes é justamente isso que você não quer.

LEFT JOIN

O LEFT JOIN (ou LEFT OUTER JOIN) mantém todas as linhas da tabela da esquerda (a que vai no FROM), tenham ou não par na direita. Quando não há correspondência, as colunas da tabela direita são preenchidas com NULL.

SELECT categorias.nome, produtos.nome
FROM categorias
LEFT JOIN produtos ON produtos.categoria_id = categorias.id;

Aqui a esquerda é categorias. O resultado inclui todas as categorias; as que não têm nenhum produto aparecem mesmo assim, com NULL na coluna do produto:

Quando usá-lo

LEFT JOIN é a ferramenta para responder perguntas do tipo "quais linhas da esquerda NÃO têm par?". Filtramos pelo NULL resultante:

-- Categorias SEM nenhum produto.
SELECT categorias.nome
FROM categorias
LEFT JOIN produtos ON produtos.categoria_id = categorias.id
WHERE produtos.id IS NULL;

Para verificar se algo é nulo, usa-se IS NULL / IS NOT NULL, nunca = NULL (em SQL, NULL = NULL não é verdadeiro).

O lado importa: A LEFT JOIN B mantém todas as de A. Se você quisesse manter todas as de B, ou inverte a ordem, ou usa RIGHT JOIN (que é o simétrico).

Exemplos

LEFT JOIN: todas as categorias, tenham produtos ou não

SELECT categorias.nome AS categoria, produtos.nome AS produto
FROM categorias
LEFT JOIN produtos ON produtos.categoria_id = categorias.id
ORDER BY categorias.nome, produtos.nome;