Los JOINs de SQL no son diagramas de Venn

Todos aprendimos los JOINs con dos círculos que se traslapan. Es una imagen bonita, fácil de recordar y equivocada — o al menos incompleta en el punto exacto donde uno se confunde.

El diagrama de Venn representa conjuntos, y en un conjunto cada elemento aparece una sola vez. Las tablas de SQL no son conjuntos: son multiconjuntos donde el mismo valor puede repetirse. Y ahí es donde el modelo mental truena.

La prueba en diez segundos

CREATE TABLE a (id INT);
CREATE TABLE b (id INT);

INSERT INTO a VALUES (1), (1);
INSERT INTO b VALUES (1), (1), (1);

SELECT COUNT(*) FROM a INNER JOIN b ON a.id = b.id;

Según el diagrama de Venn, la intersección de {1} con {1} es {1}: un elemento. El resultado real es 6.

Qué hace realmente un JOIN

Un JOIN no interseca conjuntos. Recorre las filas de la izquierda y, para cada una, busca todas las filas de la derecha que cumplen la condición. Cada coincidencia produce una fila de salida.

Con dos filas a la izquierda y tres coincidencias cada una: 2 × 3 = 6.

El modelo mental que sí funciona

Piensa en filas y en flechas, no en círculos:

• INNER JOIN — solo las filas de la izquierda que encontraron pareja. Cuantas parejas encuentre, tantas filas salen.
• LEFT JOIN — todas las de la izquierda. Las que no encontraron pareja salen igual, con NULL en las columnas de la derecha.
• RIGHT JOIN — lo mismo pero al revés. Casi nadie lo usa: es más legible voltear el orden de las tablas y quedarte con LEFT.
• FULL OUTER JOIN — todas las de ambos lados, rellenando con NULL lo que falte de cada lado.
• CROSS JOIN — cada fila de la izquierda contra cada fila de la derecha, sin condición. Es el producto cartesiano, y es lo que obtienes por accidente cuando olvidas el ON.

Cómo detectar la duplicación antes de que llegue al reporte

Antes de confiar en un JOIN, verifica que el lado derecho sea único por la llave con la que estás uniendo:

-- Si esto devuelve algo, tu JOIN va a multiplicar filas.
SELECT id_cliente, COUNT(*)
FROM dim_cliente
GROUP BY id_cliente
HAVING COUNT(*) > 1;

Y compara el conteo antes y después. Si un LEFT JOIN te cambió el número de filas de la tabla izquierda, se duplicaron:

SELECT
  (SELECT COUNT(*) FROM pedidos)                     AS antes,
  (SELECT COUNT(*) FROM pedidos p
     LEFT JOIN dim_cliente c ON c.id_cliente = p.id_cliente) AS despues;

Un LEFT JOIN correcto nunca reduce el conteo de la izquierda, y si lo aumenta es que el lado derecho tenía duplicados.

La trampa del WHERE con LEFT JOIN

Esta merece su propia advertencia:

-- Esto NO es un LEFT JOIN: el WHERE lo convirtió en INNER.
SELECT *
FROM pedidos p
LEFT JOIN envios e ON e.id_pedido = p.id_pedido
WHERE e.estatus = 'entregado';

Las filas sin envío salen del LEFT JOIN con e.estatus en NULL, y NULL = 'entregado' no es verdadero, así que el WHERE las elimina. Si quieres conservarlas, la condición va en el ON:

SELECT *
FROM pedidos p
LEFT JOIN envios e
  ON e.id_pedido = p.id_pedido
 AND e.estatus = 'entregado';

Esa es la regla: en un LEFT JOIN, las condiciones sobre la tabla derecha van en el ON, no en el WHERE.