Cómo funciona un JOIN en SQL: filas, INNER vs LEFT y NULL

La idea

Un JOIN responde una pregunta para cada fila de la primera tabla: ¿qué filas de la segunda tabla van con esta? Tú le dices cómo decidirlo en la cláusula ON. Luego la base de datos crea una fila de resultado por cada par que pasa la prueba.

Eso es todo lo que es un JOIN. No se pegan tablas una al lado de la otra, y ninguna fila se alinea con "la fila de al lado". Cada fila de customers se compara con las filas de orders, y cada coincidencia se convierte en una fila de resultado que lleva las columnas de ambas.

Figura 1

Unir clientes con sus pedidos, un cliente a la vez

customers
customer_id	first_name	city
69	Gary	Chicago
101	Charles	Las Vegas
151	Henry	Orlando

orders
order_id	customer_id	order_date
1231	69	2023-02-06
1734	101	2023-04-28
4230	69	2024-01-08

Resultado
first_name	order_id	order_date
Gary	1231	2023-02-06
Gary	4230	2024-01-08
Charles	1734	2023-04-28
Henry	NULL	NULL

Paso 1 de 6 Dos tablas que comparten una columna. customer_id es la clave en customers y una columna normal en orders, que apunta al cliente que hizo el pedido.

Dos tablas que comparten una columna. customer_id es la clave en customers y una columna normal en orders, que apunta al cliente que hizo el pedido.
ON c.customer_id = o.customer_id. Toma a Gary (69) y busca todos los pedidos cuyo customer_id sea 69. Hay dos.
Cada coincidencia se convierte en una fila del resultado. Gary tuvo dos coincidencias, así que Gary aparece dos veces. Su nombre se copia en ambas filas.
Charles (101) tiene un pedido, así que le toca una fila.
Henry (151) se registró pero nunca compró. Ningún pedido tiene customer_id 151. Un INNER JOIN conserva solo las filas con coincidencia, así que Henry simplemente queda fuera.
Un LEFT JOIN conserva todas las filas de la tabla de la izquierda. Henry se queda, y las columnas que habrían venido de orders no tienen nada que mostrar: son NULL.

Por qué existe

Harbor & Pine guarda cada dato en un solo lugar. El nombre y la ciudad de un cliente viven en customers, una vez. Cada pedido vive en orders, una vez, con un customer_id que dice quién lo hizo. Las líneas de un pedido, los productos, las devoluciones: cada cosa tiene su propia tabla, conectada por ids.

Ese diseño mantiene los datos correctos. Si Gary se muda de Chicago a Denver, cambia una sola fila, y todos los pedidos que ha hecho siguen siendo suyos. Pero casi todas las preguntas reales cruzan tablas: los ingresos por categoría necesitan order_items y products; "pedidos por ciudad" necesita orders y customers. El JOIN es la forma de volver a juntar las piezas para una pregunta, sin guardarlas juntas.

Cómo funciona por dentro

Imagina que la base de datos recorre la tabla de la izquierda fila por fila.

Toma una fila de la tabla de la izquierda, por ejemplo Gary, customer_id 69.
Busca sus coincidencias en la tabla de la derecha: todas las filas donde la condición ON es verdadera. Para ON o.customer_id = c.customer_id, son todos los pedidos con customer_id 69.
Genera una fila de resultado por coincidencia. Dos pedidos, dos filas. El nombre y la ciudad de Gary se copian en cada una.
Decide qué hacer con las filas sin coincidencia. Esta es la única diferencia entre los tipos de join. Un INNER JOIN no genera nada para ellas. Un LEFT JOIN genera la fila de la izquierda una vez, con NULL en cada columna que habría venido de la tabla de la derecha.

Las bases de datos reales no recorren las filas literalmente así. Construyen tablas hash, ordenan y usan índices, así que unir 12,000 pedidos con 2,000 clientes toma milisegundos. Aun así, el resultado es exactamente el que daría ese recorrido lento, y ese recorrido es la imagen correcta para tener en la cabeza.

Las coincidencias deciden el número de filas

El paso 3 es de donde salen la mayoría de las sorpresas con los joins. Una fila aparece en el resultado una vez por coincidencia, así que:

un cliente sin pedidos: 0 filas (INNER) o 1 fila con NULL (LEFT);
un cliente con un pedido: 1 fila;
un cliente con cinco pedidos: 5 filas.

Cuando la tabla de la derecha tiene muchas filas por clave, los valores de la tabla de la izquierda se repiten. No hay problema mientras cuentes o sumes columnas de la tabla de la derecha. Todo se tuerce en cuanto sumas una columna que pertenece a la tabla de la izquierda.

Figura 2

Un pedido, tres líneas: un join uno a muchos repite el pedido

orders
order_id	channel	shipping_fee
1049	marketplace	4.99

order_items
order_item_id	order_id	quantity
139	1049	2
140	1049	2
141	1049	3

Resultado del join
order_id	order_item_id	shipping_fee
1049	139	4.99
1049	140	4.99
1049	141	4.99

SUM(shipping_fee) después del join
shipping_fee
14.97

Paso 1 de 4 El pedido 1049 pagó un solo costo de envío de $4.99. Tiene tres líneas de pedido en order_items.

El pedido 1049 pagó un solo costo de envío de $4.99. Tiene tres líneas de pedido en order_items.
Al unir por order_id, la única fila del pedido coincide con las tres líneas.
Tres coincidencias, tres filas en el resultado. Las columnas del pedido se copian en cada una, shipping_fee incluida.
Suma shipping_fee ahora y obtienes $14.97 por un pedido que pagó $4.99. El join no tiene nada malo. El total está sumando una copia por línea.

Esto se llama fan-out (multiplicación de filas). El costo de envío es por pedido, pero después de unir con order_items hay una copia por línea. Suma el costo por pedido primero y une después, o cuenta con COUNT(DISTINCT o.order_id) cuando necesites pedidos y no líneas.

De dónde salen los NULL

Un NULL en el resultado de un LEFT JOIN significa "esta fila no tuvo coincidencia", no "el valor está vacío en la base de datos". El order_id de Henry es NULL porque no hay ningún pedido del cual tomarlo. Eso es útil: WHERE o.order_id IS NULL después de un LEFT JOIN es la forma estándar de encontrar clientes que nunca compraron, productos que nunca se vendieron o sesiones que nunca terminaron en compra.

Revisa un join antes de confiar en él

Dos conteos rápidos detectan casi cualquier error en un join. Cuenta las filas de la tabla de la izquierda y luego cuenta las filas del join. Si los conteos son iguales, cada fila de la izquierda encontró como mucho una coincidencia. Un conteo mayor significa que alguna clave se repite del lado derecho, lo cual es normal de clientes a pedidos y es un error de pedidos a una tabla que creías que tenía una fila por pedido. Después cuenta los NULL en una columna clave de la tabla de la derecha: así sabes cuántas filas de la izquierda no encontraron ninguna coincidencia.

El orden en que se ejecuta SQL

Los NULL también explican el error más común con LEFT JOIN. SQL ejecuta primero el FROM y los joins, y el WHERE después, sobre las filas ya unidas.

Figura 3

Por qué un WHERE sobre la tabla de la derecha deshace un LEFT JOIN

FROM customers c todos los clientes
LEFT JOIN orders o ON … Henry se queda, con NULL
WHERE o.status = 'completed' NULL = 'completed' no es verdadero
SELECT … lo que queda

Paso 1 de 4 La base de datos arma primero las filas unidas. FROM y el LEFT JOIN se ejecutan antes de cualquier filtro, y Henry está ahí con las columnas del pedido en NULL.

La base de datos arma primero las filas unidas. FROM y el LEFT JOIN se ejecutan antes de cualquier filtro, y Henry está ahí con las columnas del pedido en NULL.
Luego WHERE revisa cada fila unida. Para Henry, o.status es NULL, y NULL = 'completed' es desconocido, algo que WHERE trata como falso.
La fila de Henry desaparece antes de que SELECT llegue a verla. El LEFT JOIN ahora devuelve exactamente lo mismo que un INNER JOIN.
La solución: mueve la condición al ON: LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'completed'. Así decide qué pedidos coinciden, y todos los clientes se quedan.

Una condición sobre la tabla de la derecha en el WHERE compara los NULL con un valor. La comparación nunca es verdadera, así que las filas que el LEFT JOIN conservó se descartan. Pon las condiciones sobre la tabla de la derecha en el ON cuando quieras conservar todas las filas de la izquierda.

Errores comunes

"Un JOIN pone las dos tablas una al lado de la otra"

Las filas se emparejan por la condición ON, nunca por su posición. Revuelve orders y el resultado es el mismo conjunto de filas. Si dos tablas resultan estar en el mismo orden y esperas que la fila 1 vaya con la fila 1, estás pensando en pegar columnas en una hoja de cálculo, que es otra cosa (y frágil).

"Un LEFT JOIN devuelve tantas filas como la tabla de la izquierda"

Devuelve al menos una fila por cada fila de la izquierda. Un cliente con tres pedidos sigue produciendo tres filas. Compruébalo en cada join que escribas: cuenta las filas antes y después. Si el conteo creció, una clave que creías única no lo es.

"NULL en el resultado significa que faltan datos"

En el resultado de un INNER JOIN, un NULL solo puede venir de los propios datos. En el resultado de un LEFT JOIN normalmente significa "sin coincidencia". Antes de rellenar los NULL con 0 o "desconocido", pregúntate cuál de los dos es: un cliente sin pedidos tiene cero ingresos, que es una respuesta real, mientras que una ciudad que falta es un problema de datos.

Ponte a prueba

Una pregunta. Al responderla, la lección queda marcada en el mapa; nunca bloquea un nivel.

El cliente 151 no tiene pedidos. Ejecutas

SELECT c.first_name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NOT NULL

. ¿Aparece el cliente 151 en el resultado? Sí, con order_id en NULL, porque un LEFT JOIN siempre conserva a todos los clientes No: el WHERE se ejecuta después del join y elimina la fila donde order_id es NULL Sí, dos veces, porque un LEFT JOIN agrega una fila extra para los clientes sin pedidos No: el propio LEFT JOIN descarta a los clientes sin coincidencia