En la lección anterior perdiste tres clientes, tres productos y diez pedidos sin que nadie te avisara. El LEFT JOIN es la herramienta que los recupera. Su regla es una sola frase: conserva todas las filas de la tabla izquierda, casen o no casen con la derecha; cuando no casan, las columnas de la derecha se rellenan con NULL.

Suena sencillo, y lo es. Pero el LEFT JOIN esconde la trampa más famosa de todo SQL intermedio: poner una condición en el ON o en el WHERE deja de ser indiferente. La diferencia no da error, no da aviso, y produce dos resultados distintos que parecen igualmente plausibles. Media lección está dedicada a que no caigas nunca en ella.

1. La regla del LEFT JOIN y el diagrama de conjuntos
2. LEFT JOIN = LEFT OUTER JOIN
3. De dónde salen exactamente los NULL
4. Los tres casos reales de TiendaVerde
5. El patrón anti-join: encontrar lo que no casa
6. La trampa: condición en ON frente a condición en WHERE
7. LEFT JOIN encadenados
8. Cuando la tabla derecha tiene varias filas por cada izquierda
9. Errores Comunes y Consejos
10. Ejercicios
11. Conclusión

Como flujo de filas, ampliando el diagrama de 03-02:

Ese paso extra —"se añaden igualmente con NULL a la derecha"— es el paso 1d del orden lógico que viste en 03-01. Ocurre dentro del FROM, después de aplicar el ON y antes del WHERE. Todo lo que explica esta lección se deduce de ese hecho.

La palabra "izquierda" es literal: es la tabla que escribes antes de la palabra LEFT JOIN.

De aquí sale la consecuencia más importante para escribir bien: el LEFT JOIN no es conmutativo. clientes LEFT JOIN pedidos conserva los 15 clientes; pedidos LEFT JOIN clientes conserva los 20 pedidos. Son preguntas distintas.

Cómo elegir el lado izquierdo: ponte la pregunta de negocio en voz alta y busca el sustantivo que debe aparecer completo en el informe. "Quiero todos los clientes, con sus pedidos si los tienen" → clientes es la izquierda. "Quiero todos los pedidos, con su comercial si lo tienen" → pedidos es la izquierda.

Las dos escrituras son idénticas:

OUTER es opcional y prácticamente nadie lo escribe. El adjetivo externo (outer) describe la familia entera: LEFT, RIGHT y FULL son joins externos porque conservan filas que quedan fuera del emparejamiento; INNER es el join interno porque solo devuelve lo que queda dentro.

En este curso escribimos LEFT JOIN.

Este punto se malinterpreta a menudo, así que conviene ser preciso: los NULL que aparecen en un LEFT JOIN no estaban en la base de datos. Los fabrica el motor en el momento de construir el resultado.

Cuando una fila de la izquierda no encuentra pareja, PostgreSQL la incorpora igualmente al resultado y rellena todas las columnas de la tabla derecha con NULL. No solo la de la clave: todas.

De ahí salen dos consecuencias que usarás constantemente:

1. Puedes detectar la ausencia mirando cualquier columna de la derecha. Si pe.id IS NULL en un LEFT JOIN desde clientes, es que no hubo pareja. Es la base del anti-join de la sección 5.
2. Conviene mirar una columna NOT NULL de la derecha. Si eligieras una columna que puede ser nula de verdad en los datos, no sabrías distinguir "no hubo pareja" de "hubo pareja y ese dato estaba vacío". Por eso el anti-join se escribe siempre contra la clave primaria de la tabla derecha: pe.id, lp.id, r.id. Una PK nunca es NULL de forma legítima.

Un tercer efecto, más sutil, es la aritmética con nulos que viste en 02-02: cualquier operación con NULL da NULL. Si calculas pe.gastos_envio * 2 sobre una fila sin pareja, el resultado es NULL, no cero. La función COALESCE que resuelve esto se estudia en 06-04; el tratamiento completo de los nulos, en 04-03.

Los tres huecos deliberados del conjunto de datos existen precisamente para esta lección.

23 filas. Compara con 03-02:

Ahí están Núria, Hugo e Inés, con toda la parte de pedidos en NULL. La aritmética es exacta y merece la pena interiorizarla: el resultado de un LEFT JOIN tiene tantas filas como el INNER JOIN más una fila por cada fila izquierda huérfana.

Mismo patrón, ahora en el catálogo. Recortamos a cinco productos para ver el contraste con claridad:

Los productos 12 y 18 se han vendido tres veces cada uno y ocupan tres filas. Los productos 13, 19 y 20 nunca se han vendido y ocupan una fila con todo a NULL.

Sin el WHERE de recorte, la consulta completa devuelve 50 filas: las 47 de lineas_pedido más las 3 de los productos nunca vendidos.

El tercer caso es distinto de los dos anteriores, y conviene fijarse en el matiz. Aquí no falta la fila hija: la clave foránea es NULL.

20 filas: los 20 pedidos. Frente a las 10 que devolvía el INNER JOIN de 03-02. Los NULL de la columna comercial significan aquí algo perfectamente legible para el negocio: pedido entrado por la web, sin comercial asignado. Diez de veinte, exactamente la proporción que 01-06 describía.

Fíjate en un detalle: comercial vale NULL por partida doble. Primero porque pe.empleado_id es NULL y no casa con nada; y segundo porque, aunque casara, la concatenación e.nombre || ' ' || e.apellidos sobre columnas nulas da NULL (la trampa de 02-02).

Hasta ahora hemos usado el LEFT JOIN para conservar lo que no casa. Ahora vamos a usarlo para quedarnos solo con eso, que es una de las consultas más pedidas en cualquier empresa: clientes inactivos, productos sin rotación, facturas sin cobrar, usuarios sin verificar.

La técnica se llama anti-join y se escribe en dos movimientos:

1. Un LEFT JOIN que conserva todo lo de la izquierda.
2. Un WHERE <clave primaria de la derecha> IS NULL que se queda solo con las filas que no encontraron pareja.

Tres filas. Esta es exactamente la lista que pediría el departamento de marketing para lanzar una campaña de primera compra.

Tres filas, y con un diagnóstico distinto para cada una: las velas no se venden porque no hay stock; la espirulina porque está descatalogada (activo = false); el desodorante tiene stock y está activo, así que su problema es comercial. Un JOIN bien planteado no solo devuelve datos: apunta a la causa.

Sobre IS NULL: es el operador correcto para preguntar por nulos, porque = NULL nunca es verdadero (lo viste en 02-03 con empleado_id = NULL devolviendo 0 filas). Su tratamiento completo, junto con IS NOT NULL y COALESCE, es la lección 04-03.

Nota: en el módulo 7 verás otras dos formas de escribir lo mismo: NOT EXISTS con una subconsulta correlacionada, y NOT IN. Las tres tienen el mismo objetivo y distinto comportamiento ante los nulos. El anti-join con LEFT JOIN es el que puedes escribir hoy, y es perfectamente idiomático.

Aquí está el contenido más importante de la lección. Presta atención a la pregunta, porque la clave está en ella:

"Dame todos los clientes, con sus pedidos entregados."

Fíjate en el "todos": el informe debe listar a los 15 clientes, tengan o no pedidos entregados. Vamos a escribirlo de las dos maneras posibles.

18 filas y los 15 clientes presentes. Los que no tienen ningún pedido entregado aparecen con NULL: Núria, Hugo e Inés porque no han comprado nunca, y Ana Belmonte Roca porque sus dos pedidos están cancelado y pagado, ninguno entregado. Ana es el caso más interesante: existe en pedidos, pero ninguno de sus pedidos supera la condición del ON.

Cambiamos exactamente dos palabras: AND pasa a ser WHERE.

14 filas y solo 12 clientes. Han desaparecido Ana, Núria, Hugo e Inés. El LEFT JOIN se ha comportado como un INNER JOIN.

Vuelve al orden lógico de 03-01 y sigue el recorrido de la fila de Núria en cada versión:

El paso 1d sí añade a Núria en las dos versiones. La diferencia está después: en la versión B, el WHERE evalúa pe.estado = 'entregado' sobre una fila cuyo pe.estado es NULL. Y NULL = 'entregado' no es FALSE, es NULL, que tampoco es TRUE, así que la fila se descarta. Es el mismo mecanismo de 02-03 actuando en un sitio nuevo.

La regla que hay que grabar: en un LEFT JOIN, cualquier condición en el WHERE sobre una columna de la tabla derecha convierte el LEFT en un INNER, porque las filas rellenadas con NULL no pueden satisfacerla.

Las tres son válidas: cada una responde a una pregunta distinta. El error no es usar el WHERE, es usarlo creyendo que hace lo del ON.

Un caso que sí es seguro: una condición en el WHERE sobre una columna de la tabla izquierda no degrada nada. WHERE c.pais = 'España' filtra clientes, y los que pasen el filtro conservan su NULL a la derecha sin problema. El peligro es exclusivamente el de las columnas de la derecha.

Con tres o más tablas el LEFT JOIN mantiene su lógica, pero aparece una regla que sorprende: un INNER JOIN colocado después de un LEFT JOIN anula el efecto del LEFT.

Queremos "todos los clientes, con sus pedidos y el comercial de cada pedido". Escrito con LEFT en los dos saltos:

Y ahora el mismo camino con un INNER JOIN en el segundo salto:

El razonamiento: los JOIN se resuelven de izquierda a derecha, así que (clientes LEFT JOIN pedidos) produce 23 filas; sobre esas 23, el INNER JOIN con empleados exige que pe.empleado_id case con un empleado. Las 3 filas de clientes sin pedidos tienen pe.empleado_id = NULL y caen; las 10 filas de pedidos web tienen empleado_id = NULL y también caen. Quedan 10.

Regla práctica: una vez que has abierto una cadena con LEFT JOIN, todos los saltos posteriores sobre esa rama deben ser LEFT JOIN. Un solo INNER en medio deshace todo el trabajo, y lo hace sin ningún aviso.

Es un error muy fácil de cometer al ampliar una consulta existente: alguien añade JOIN productos ON ... al final de una consulta que empezaba con LEFT JOIN, y el informe pierde filas de un día para otro sin que nadie toque el LEFT.

El LEFT JOIN no protege de la multiplicación de filas de 03-02. Sigue habiendo una fila por pareja:

Cuatro productos producen siete filas: dos reseñas para los productos 1, 2 y 6, y una fila vacía para el 4. Sobre el catálogo entero, productos LEFT JOIN resenas devuelve 23 filas: las 12 reseñas más los 11 productos sin ninguna.

La cuenta general de un LEFT JOIN es siempre esta:

Y de nuevo el aviso del módulo 4: si sobre el primer caso sumaras c.fecha_registro, o contaras clientes, obtendrías cifras infladas, porque Lucía aparece tres veces. Un LEFT JOIN conserva filas; no las deduplica.

• Poner una condición sobre la tabla derecha en el WHERE. Degrada el LEFT JOIN a INNER JOIN sin decir nada. Es el error de esta lección: 18 filas contra 14, y cuatro clientes desaparecidos.
• Mezclar un INNER JOIN después de un LEFT JOIN. Mismo efecto, en cadena: 23 filas se convierten en 10.
• Escribir las tablas al revés. pedidos LEFT JOIN clientes no es clientes LEFT JOIN pedidos. El LEFT JOIN no es conmutativo; la izquierda es la que se conserva entera.
• Hacer el anti-join contra una columna que admite nulos. WHERE pe.empleado_id IS NULL no distingue "no hubo pareja" de "hubo pareja con el dato vacío". Compara siempre contra la clave primaria de la derecha.
• Intentar un anti-join con INNER JOIN. INNER JOIN ... WHERE derecha.id IS NULL devuelve siempre cero filas: el INNER ya ha tirado esas filas.
• Usar = NULL en lugar de IS NULL. WHERE pe.id = NULL devuelve cero filas siempre (02-03).
• Suponer que NULL en el resultado significa cero. pe.gastos_envio es NULL, no 0.00, para un cliente sin pedidos. Operar con él propaga el nulo (COALESCE, en 06-04).
• Olvidar que el LEFT JOIN sigue multiplicando filas. Conserva a los huérfanos, pero no impide que un cliente con tres pedidos ocupe tres filas.
• Consejo: escribe primero el INNER JOIN, comprueba el recuento, y luego cámbialo a LEFT. La diferencia entre ambos recuentos te dice exactamente cuántos huérfanos hay.
• Consejo: para el anti-join, di la pregunta en voz alta. "Clientes sin pedidos", "productos nunca vendidos", "facturas sin cobrar. El "sin" y el "nunca" son la señal de que toca LEFT JOIN ... WHERE ... IS NULL.
• Consejo: sospecha de cualquier WHERE que nombre la tabla derecha de un LEFT JOIN. Salvo que sea un IS NULL deliberado, casi siempre debería estar en el ON.

El equipo de producto quiere saber qué referencias del catálogo no han recibido ninguna reseña, para lanzar una campaña de solicitud de opiniones. Escribe la consulta que devuelva el id, el nombre, la categoría y el precio de esos productos, ordenados por id.

Después responde: ¿por qué el JOIN con categorias puede ser un INNER JOIN sin que eso rompa el anti-join?

Logística necesita el listado completo de los 20 pedidos con la información de su devolución, si la hubo: id del pedido, fecha, estado, gastos de envío y —cuando exista devolución— su motivo e importe.

1. Escribe la consulta.
2. Escribe después la variante que devuelve solo los pedidos que no tuvieron devolución, e indica cuántas filas da.

Un compañero te pasa esta consulta y te dice: "quiero todos los clientes con sus pedidos pagados por tarjeta, pero me faltan clientes".

1. Explica qué está haciendo realmente esa consulta.
2. Corrígela para que devuelva lo que él quiere.
3. Predice cuántas filas devuelve cada versión y cuántos clientes distintos aparecen en cada una.

Solución 1

11 filas, exactamente los 11 productos sin reseña que anunciaba 01-06. Solo 9 de los 20 productos han sido reseñados alguna vez.

Por qué el JOIN con categorias puede ser INNER: porque la sección 7 advierte de los INNER JOIN que aparecen después de un LEFT JOIN sobre la misma rama. Aquí no es el caso: categorias cuelga de productos por una FK que apunta a una categoría que siempre existe, así que ese INNER JOIN no descarta ningún producto. Las 20 filas de partida siguen siendo 20 antes de aplicar el LEFT JOIN con resenas. La regla precisa es: un INNER JOIN es seguro mientras no pueda eliminar filas de la tabla protagonista.

(Si categoria_id admitiera NULL con datos reales nulos, ese INNER JOIN sí perdería productos y habría que escribirlo también como LEFT JOIN.)

Solución 2

1. Todos los pedidos con su devolución, si la hubo:

Devuelve 20 filas: las 3 con devolución (pedidos 6, 10 y 13) muestran motivo e importe; las otras 17 muestran *(null)* en esas dos columnas.

2. Solo los pedidos sin devolución (anti-join):

17 filas = 20 pedidos − 3 devoluciones. Faltan justo los pedidos 6, 10 y 13.

Solución 3

1. Qué hace realmente. La condición pe.metodo_pago = 'tarjeta' está en el WHERE y se aplica a una columna de la tabla derecha. Toda fila de cliente sin pedido llega al WHERE con pe.metodo_pago a NULL, y NULL = 'tarjeta' no es TRUE. Resultado: el LEFT JOIN se degrada a INNER JOIN y la consulta devuelve solo los clientes que tienen al menos un pedido pagado con tarjeta. Es una pregunta legítima, pero no la que él quería.

2. Corrección: mover la condición al ON.

3. Predicción de filas. Los pedidos con metodo_pago = 'tarjeta' son los ids 1, 3, 5, 6, 8, 10, 11, 13, 15, 16 y 19: 11 pedidos, de 9 clientes distintos (Lucía aparece tres veces).

Los 6 clientes que aparecen con NULL en la versión corregida son: Tiago (8), Julien (10), Diego (12), Núria (13), Hugo (14) e Inés (15). Los tres últimos porque no han comprado nunca; los tres primeros porque compraron, pero pagando con PayPal o transferencia.

El LEFT JOIN es, en la práctica, el JOIN que más problemas de negocio resuelve:

• Conserva todas las filas de la tabla izquierda, casen o no, rellenando con NULL las columnas de la derecha. LEFT JOIN y LEFT OUTER JOIN son lo mismo.
• Los NULL los fabrica el motor al construir el resultado; no estaban en los datos. Por eso puedes detectar la ausencia mirando la clave primaria de la tabla derecha.
• Has recuperado los tres huecos de TiendaVerde: los clientes 13, 14 y 15 (23 filas en vez de 20), los productos 13, 19 y 20 (50 filas en vez de 47) y los 10 pedidos web (20 filas en vez de 10).
• El patrón anti-join LEFT JOIN ... WHERE derecha.id IS NULL responde a las preguntas con "sin" y "nunca": tres clientes que no han comprado, tres productos que nadie ha vendido, once productos sin reseñas.
• Sabes que una condición sobre la tabla derecha en el WHERE degrada el LEFT JOIN a INNER JOIN: 18 filas y 15 clientes contra 14 filas y 12 clientes, con la misma pregunta escrita de dos formas. Si quieres "todos los X con sus Y que cumplan Z", la condición va en el ON.
• Un INNER JOIN encadenado después de un LEFT JOIN anula su efecto: 23 filas se quedan en 10. Abierta una rama con LEFT, sigue con LEFT.
• El LEFT JOIN no deduplica: sigue multiplicando filas cuando la derecha tiene varias por cada izquierda.

En la lección siguiente, RIGHT JOIN, veremos el simétrico exacto de lo que acabas de aprender. Comprobarás que A RIGHT JOIN B y B LEFT JOIN A devuelven exactamente lo mismo, entenderás por qué la mayoría de guías de estilo prefiere el LEFT pese a ello, y verás por fin a Irene Salvador Mira y a Daniel Vercher Lluch, los dos empleados que nunca han gestionado un pedido.