LEFT JOIN: conservar la tabla izquierda

El problema del INNER JOIN

El INNER JOIN descarta las filas sin pareja. Si una categoría no tiene ningún producto, no aparece. Si un producto no tiene categoría asignada (categoria_id es NULL), tampoco. A veces eso es justo lo que no quieres.

LEFT JOIN

El LEFT JOIN (o LEFT OUTER JOIN) conserva todas las filas de la tabla de la izquierda (la que va en el FROM), tengan o no pareja en la derecha. Cuando no hay coincidencia, las columnas de la tabla derecha se rellenan con NULL.

SELECT categorias.nombre, productos.nombre
FROM categorias
LEFT JOIN productos ON productos.categoria_id = categorias.id;

Aquí la izquierda es categorias. El resultado incluye todas las categorías; las que no tienen ningún producto aparecen igualmente, con NULL en la columna del producto:

Cuándo usarlo

LEFT JOIN es la herramienta para responder preguntas del tipo "¿qué filas de la izquierda NO tienen pareja?". Filtramos por el NULL resultante:

-- Categorías SIN ningún producto.
SELECT categorias.nombre
FROM categorias
LEFT JOIN productos ON productos.categoria_id = categorias.id
WHERE productos.id IS NULL;

Para comprobar si algo es nulo se usa IS NULL / IS NOT NULL, nunca = NULL (en SQL, NULL = NULL no es verdadero).

El lado importa: A LEFT JOIN B conserva todas las de A. Si quisieras conservar todas las de B, o intercambias el orden, o usas RIGHT JOIN (que es el simétrico).

Ejemplos

LEFT JOIN: todas las categorías, tengan productos o no

SELECT categorias.nombre AS categoria, productos.nombre AS producto
FROM categorias
LEFT JOIN productos ON productos.categoria_id = categorias.id
ORDER BY categorias.nombre, productos.nombre;