Joins: cómo pensarlos

El join es la operación que justifica el modelo relacional: los datos se guardan separados, sin repetirse, y se vuelven a combinar cuando hacen falta. También es donde aparecen los resultados que tienen más filas de las esperadas, y casi siempre por la misma razón.

Producto cartesiano y después filtro

El modelo mental correcto es el del álgebra: un join es el producto cartesiano de las dos tablas —cada fila con cada fila— seguido de un filtro que deja sólo los pares que cumplen la condición.

Nadie lo ejecuta así, pero pensarlo así aclara todo. Si una fila de la izquierda hace pareja con tres de la derecha, aparece tres veces. El resultado no tiene “las filas de la tabla A”: tiene pares.

Los tipos, y qué agrega cada uno

INNER JOIN devuelve sólo los pares que coinciden. LEFT JOIN agrega, además, las filas de la izquierda que no encontraron pareja, completando con nulos las columnas de la derecha. RIGHT es lo mismo espejado y se usa poco: conviene dar vuelta las tablas y escribir siempre LEFT, que se lee mejor.

FULL OUTER conserva las huérfanas de los dos lados. Y CROSS JOIN es el producto cartesiano sin condición: útil para generar combinaciones —todos los días por todas las sucursales—, y en general un accidente cuando aparece sin querer.

La condición va en el ON, no en el WHERE

La trampa clásica del LEFT JOIN: una condición sobre la tabla derecha puesta en el WHERE convierte el join en interno. Las filas sin pareja tienen nulos en esas columnas, y el filtro las elimina.

La regla es que las condiciones sobre la tabla preservada van en el WHERE, y las condiciones sobre la tabla opcional van en el ON. En un INNER JOIN da lo mismo dónde se pongan; en un outer, cambia el resultado.

El total inflado y de dónde sale

El síntoma más frecuente es un total inflado: se suman importes después de un join y el número da de más. Casi siempre es porque el join es uno a muchos y cada fila de la izquierda se duplicó.

La forma de detectarlo es contar antes y después. Y la solución habitual es agregar primero y unir después: calcular la suma por cliente en una subconsulta y hacer join contra ese resultado, en vez de unir todo y agregar al final. Con dos joins uno a muchos sobre la misma tabla base, el efecto se multiplica entre sí y el resultado no tiene arreglo por filtro.

Las tres formas de pedir los que no tienen

Para “los que no tienen” hay tres formas. LEFT JOIN con WHERE columna IS NULL es la clásica. NOT EXISTS suele ser más clara y el motor la resuelve bien.

NOT IN es la que hay que evitar: si la subconsulta devuelve algún nulo, el resultado es vacío, porque “distinto de desconocido” nunca es verdadero. Es un bug silencioso que aparece el día que alguien permite nulos en esa columna.

Las tres maneras en que el motor lo resuelve

El motor implementa el join de tres maneras. Nested loops: recorrer una tabla y buscar en la otra por índice; ideal cuando un lado es chico. Hash join: armar una tabla hash con la tabla menor y recorrer la mayor; el caballito de batalla para tablas grandes sin índice útil. Merge join: si ambas vienen ordenadas por la clave, recorrerlas en paralelo.

Cuál elige depende de tamaños, índices y estadísticas. Lo que sí está en manos de uno es tener indexadas las columnas de la condición: casi siempre la foránea, que muchos motores no indexan solos aunque la declaren.

La condición va en el ON o en el WHERE, y no da lo mismo

-- Todos los clientes, con sus facturas impagas si las tienen
SELECT c.id, c.name, i.id AS invoice_id
FROM customers c
LEFT JOIN invoices i
       ON i.customer_id = c.id
      AND i.status = 'unpaid';   -- filtra QUÉ facturas se unen

-- Lo mismo, con la condición en el WHERE: ya no es un left join
SELECT c.id, c.name, i.id AS invoice_id
FROM customers c
LEFT JOIN invoices i ON i.customer_id = c.id
WHERE i.status = 'unpaid';       -- descarta las filas con NULL: se volvió inner

Las dos consultas se leen casi igual y devuelven cosas distintas. En la primera, un cliente sin facturas impagas aparece con invoice_id en nulo. En la segunda desaparece, porque el WHERE corre después del join y NULL = 'unpaid' no es verdadero.

La regla es directa: en un LEFT JOIN, las condiciones sobre la tabla de la derecha van en el ON. Si van en el WHERE, el left join se convierte en inner sin que nada lo indique.

Cómo los ejecuta el motor

Eso explica dos cosas prácticas. La primera: un join sin índice en la clave foránea obliga a bucles anidados sobre una tabla sin índice, que es cuadrático; agregar ese índice suele ser la diferencia entre segundos y milisegundos. La segunda: un hash join necesita memoria para la tabla hash, y si no entra, el motor la vuelca a disco y la consulta se desploma.

Cierre

Un join produce pares, no filas de una tabla: de ahí salen las duplicaciones y los totales inflados. En los outer, la condición sobre la tabla opcional va en el ON; para “los que no tienen”, NOT EXISTS antes que NOT IN; y la columna de la condición, indexada.