Tipos de JOIN en SQL Explicados con Ejemplos

Un JOIN mal escrito no lanza un error. Se ejecuta, devuelve un resultado, y las filas están mal: faltan clientes, los totales están inflados, hay registros huérfanos ocultos. Casi siempre la consulta pidió exactamente lo que obtuvo, y quien la escribió eligió el tipo de join equivocado, o filtró en el lugar equivocado.

Un join combina filas de dos tablas según una condición que tú escribes. Qué filas sobreviven depende del tipo de join, y los seis tipos que siguen dan seis respuestas distintas a partir de las mismas dos tablas.

customers tiene cinco personas. Tres de ellas fueron referidas por otro cliente, registrado en referred_by:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        TEXT NOT NULL,
  referred_by INT REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, name, referred_by) VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   NULL),
  (3, 'Carol', 1),
  (4, 'Dave',  1),
  (5, 'Erin',  2);

orders tiene seis pedidos. Cinco apuntan a un cliente real. El sexto, 106, apunta al cliente 99, que fue eliminado después de hacer el pedido, así que el pedido ya no corresponde a nadie:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 1, 20.00),
  (103, 2, 75.00),
  (104, 4, 30.00),
  (105, 4, 15.00),
  (106, 99, 10.00);

La forma más rápida de seguir los ejemplos es un contenedor de Postgres, montado igual que en Qué Es Docker Compose y Cómo Se Usa. Los joins son SQL estándar. Los dos puntos donde MySQL se comporta distinto se señalan más abajo.

INNER JOIN: solo las filas que coinciden en ambos lados

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Cinco filas. Carol y Erin no tienen pedidos, así que desaparecen. El pedido 106 no tiene cliente, así que también desaparece. JOIN e INNER JOIN significan lo mismo. INNER es opcional y la mayoría lo omite.

Con cinco filas no notas ninguna lentitud. En una tabla orders real, orders.customer_id necesita un índice, o cada join contra ella la recorre entera. Cómo Funcionan los Índices de Base de Datos y Cuándo Añadir Uno explica cuándo ese índice compensa.

LEFT JOIN: todas las filas de la izquierda, con o sin coincidencia

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Todos los clientes aparecen. Carol y Erin obtienen NULL en las columnas del pedido. Este es el join que más vas a escribir: “todo X, con lo que tenga de Y”.

RIGHT JOIN: todas las filas de la derecha, con o sin coincidencia

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

El pedido 106 reaparece, con NULL en lugar del nombre. Esta es la fila huérfana que el inner join ocultaba, y un right join (o el left join equivalente) es la forma de encontrarla a propósito.

Rara vez vas a ver un RIGHT JOIN en código real. a RIGHT JOIN b devuelve las mismas filas que b LEFT JOIN a, así que la mayoría de los equipos usa LEFT JOIN en todas partes y cambia el orden de las tablas. Reescribir un RIGHT JOIN así es una cuestión de consistencia. El original no está mal.

FULL OUTER JOIN: todo de ambos lados

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name NULLS LAST, o.order_id;

Ocho filas: cada coincidencia, más los clientes sin coincidencia, más el pedido sin coincidencia. Por eso es el join de la reconciliación: lo usas cuando quieres ver todo lo que no cuadra, en cualquiera de los dos lados. Fuera de ese caso, casi no lo vas a necesitar.

MySQL no tiene FULL OUTER JOIN, y tampoco acepta NULLS LAST. Para obtener las mismas filas ahí, toma el LEFT JOIN, y hazle un UNION ALL con un RIGHT JOIN filtrado con WHERE c.customer_id IS NULL, para que las filas ya coincididas no se repitan. Un UNION simple de los dos joins también funciona, pero tiene el costo de una deduplicación y además fusiona filas que en realidad son idénticas.

CROSS JOIN: cada fila emparejada con cada fila

1
2
3

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;

Cinco clientes por seis pedidos: treinta filas, sin ninguna condición de join. La mayoría de los cross joins en producción son accidentes. Alguien escribió FROM customers, orders y se olvidó del WHERE que debía conectarlas. Querrías uno a propósito para generar combinaciones, como cada talla con cada color, o cada día de un rango de fechas con cada tienda, antes de rellenar las cifras reales.

¿Cómo funciona un self-join?

customers.referred_by apunta a otra fila de la misma tabla. Para mostrar quién refirió a quién por nombre, une la tabla consigo misma bajo un segundo alias:

1
2
3
4

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
LEFT JOIN customers r ON c.referred_by = r.customer_id
ORDER BY c.name;

El join funciona exactamente igual que antes. customers c y customers r son dos alias, y el planificador los trata como dos tablas separadas que casualmente contienen las mismas filas. Es un LEFT JOIN a propósito, para que Alice y Bob, que no fueron referidos por nadie, se queden en el resultado. Cualquier tabla donde una fila apunta a otra fila de la misma tabla funciona así: empleados y jefes, categorías y categorías padre, comentarios y respuestas.

¿Por qué una cláusula WHERE convierte un LEFT JOIN en un INNER JOIN?

Quieres todos los clientes, con sus pedidos de más de 25 $ cuando tengan alguno:

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25;

Carol y Erin desaparecieron. WHERE se ejecuta después del join, y para ellas o.amount es NULL. NULL > 25 no es ni verdadero ni falso. Es desconocido, y WHERE solo conserva las filas que son verdaderas. El LEFT JOIN las había mantenido y el filtro las descartó.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Mantén todos los clientes; solo filtra cuáles de sus pedidos cuentan
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 25;

-- Mantiene a los clientes sin ningún pedido, pero no a los clientes cuyos pedidos son todos <= 25
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25 OR o.amount IS NULL;

Usa la primera. Poner la condición en el ON decide qué pedidos pueden coincidir, y cada cliente sigue obteniendo al menos una fila. La segunda versión solo deja pasar las filas en las que el join no encontró nada. Un cliente cuyo único pedido fue de 20 $ obtiene una fila con amount = 20.00, que no cumple ninguna de las dos condiciones, y ese cliente vuelve a desaparecer. Con estos datos, ambas consultas mantienen por casualidad a los cinco clientes, y así es como la segunda pasa las pruebas sin que nadie lo note.

¿Por qué SUM devuelve el total equivocado después de unir dos tablas?

Añade una tercera tabla, payments, donde un pedido puede pagarse en varias cuotas:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id   INT NOT NULL,
  amount     NUMERIC(10,2) NOT NULL
);

INSERT INTO payments (payment_id, order_id, amount) VALUES
  (1, 101, 25.00),
  (2, 101, 25.00),
  (3, 103, 75.00);

El pedido 101 es de 50 $, pagado en dos cuotas de 25 $. Ahora une las tres tablas y totaliza lo que pidió cada cliente:

1
2
3
4
5

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.name;

El total real de Alice es 70 $ (50.00 + 20.00). Fallaron dos cosas. El pedido 101 coincide con dos pagos, así que el join devuelve esa fila de pedido dos veces y SUM(o.amount) suma sus 50 $ dos veces. El pedido 102 no tiene pagos, así que el inner join contra payments lo elimina. Para Alice eso da 50 + 50 + 0 = 100. Dave no tiene ningún pago, así que no aparece en el resultado.

La regla: cuando agregas una columna, no unas tablas que la agregación no necesita. Cualquier join uno a muchos puede multiplicar filas o eliminarlas. Si solo quieres los totales de los pedidos, deja fuera payments:

1
2
3
4

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

Si necesitas también los totales de pagos en el mismo informe, primero haz un GROUP BY order_id sobre payments en una subconsulta o un CTE. Eso da una fila por pedido, y puedes unirla a los pedidos sin riesgo.

NOT IN contra NOT EXISTS: ¿cómo encuentras filas sin coincidencia?

“Clientes sin pedidos” parece un trabajo para NOT IN:

1
2

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Funciona aquí solo porque orders.customer_id es NOT NULL. Quita esa restricción e inserta un solo pedido con customer_id en NULL, y la consulta devuelve cero filas, sin ningún error. NOT IN se expande en una cadena de comparaciones <> unidas con AND. x <> NULL es desconocido, así que un solo NULL vuelve desconocida la condición entera para todos los clientes.

Dos patrones no tienen este problema:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- LEFT JOIN + IS NULL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- NOT EXISTS
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Ambas devuelven a Carol y Erin, y un NULL en orders.customer_id no afecta a ninguna de las dos. Ninguna construye una lista para comparar. Ambas comprueban, cliente por cliente, si existe un pedido correspondiente. Usa NOT EXISTS por defecto. Usa LEFT JOIN ... IS NULL cuando la consulta ya une las dos tablas para obtener otras columnas.

Cuando un join devuelve más filas de las que esperas, busca una tabla uno a muchos entre el join y el GROUP BY. Cuando devuelve menos, busca una condición WHERE sobre la tabla que puede ser NULL. En ambos casos, corrige la consulta alrededor del join antes de cambiar el tipo de join.

Artículos relacionados

• Cómo Funcionan los Índices de Base de Datos y Cuándo Añadir Uno
• Qué Es Docker Compose y Cómo Se Usa
• Cómo Funciona el Caching con Redis y Cómo Usarlo