JOIN avanzados en SQL

En la lección de JOIN aprendiste los dos tipos que usarás el 90 % del tiempo: INNER JOIN y LEFT JOIN. Aquí verás el resto de la familia: RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, cómo unir una tabla consigo misma (self join) y cómo emparejar filas con condiciones que no son una simple igualdad. Al final sabrás elegir el tipo de JOIN adecuado para cada pregunta.

Las tablas de esta lección

Con clientes y pedidos no podemos ver todos los casos: todos los pedidos tienen cliente. Por eso crearemos una pequeña empresa con departamentos y empleados. Fíjate en dos detalles: Raúl no tiene departamento y Marketing no tiene empleados. Además, cada empleado guarda en jefe_id el id de su jefe, que es otro empleado.

CREATE TABLE departamentos (
  id INT PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL
);

CREATE TABLE empleados (
  id INT PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL,
  departamento_id INT REFERENCES departamentos(id),
  jefe_id INT REFERENCES empleados(id),
  salario DECIMAL(8, 2)
);

INSERT INTO departamentos VALUES
  (1, 'Dirección'), (2, 'Ventas'), (3, 'Almacén'), (4, 'Marketing');

INSERT INTO empleados VALUES
  (1, 'Carmen', 1, NULL, 3200.00),
  (2, 'Jorge', 2, 1, 2100.00),
  (3, 'Lucía', 2, 2, 2150.00),
  (4, 'Iker', 3, 1, 1900.00),
  (5, 'Nerea', 3, 4, 1650.00),
  (6, 'Raúl', NULL, 1, 1700.00);

Repaso rápido: INNER y LEFT

Piensa en las filas de dos tablas, A (la del FROM) y B (la del JOIN), repartidas en tres zonas: las de A sin pareja, las que tienen pareja en las dos, y las de B sin pareja. Cada tipo de JOIN decide qué zonas devuelve (███):

                   solo en A   en A y en B   solo en B
INNER JOIN                        ███
LEFT JOIN             ███         ███
RIGHT JOIN                        ███           ███
FULL OUTER JOIN       ███         ███           ███
Semi-join                         ███   (EXISTS, solo columnas de A)
Anti-join             ███  (NOT EXISTS)

Iremos viendo cada fila de este diagrama con ejemplos.

INNER JOIN solo devuelve empleados que tienen departamento:

SELECT e.nombre, d.nombre AS departamento
FROM empleados e
INNER JOIN departamentos d ON e.departamento_id = d.id;

nombre	departamento
Carmen	Dirección
Jorge	Ventas
Lucía	Ventas
Iker	Almacén
Nerea	Almacén

Raúl desaparece (no tiene pareja) y Marketing también. Con LEFT JOIN conservas todos los empleados:

SELECT e.nombre, d.nombre AS departamento
FROM empleados e
LEFT JOIN departamentos d ON e.departamento_id = d.id;

nombre	departamento
Carmen	Dirección
Jorge	Ventas
Lucía	Ventas
Iker	Almacén
Nerea	Almacén
Raúl	NULL

RIGHT JOIN

RIGHT JOIN es el espejo de LEFT JOIN: conserva todas las filas de la tabla de la derecha (la que va después de JOIN), tengan o no pareja.

                   solo en A   en A y en B   solo en B
RIGHT JOIN                        ███           ███

SELECT e.nombre, d.nombre AS departamento
FROM empleados e
RIGHT JOIN departamentos d ON e.departamento_id = d.id;

nombre	departamento
Carmen	Dirección
Jorge	Ventas
Lucía	Ventas
Iker	Almacén
Nerea	Almacén
NULL	Marketing

Ahora aparece Marketing (sin empleados) y Raúl no.

Por qué casi siempre se escribe como LEFT JOIN

Cualquier RIGHT JOIN se puede reescribir como LEFT JOIN cambiando el orden de las tablas. Esta consulta da exactamente el mismo resultado:

SELECT e.nombre, d.nombre AS departamento
FROM departamentos d
LEFT JOIN empleados e ON e.departamento_id = d.id;

La mayoría de equipos prefiere usar solo LEFT JOIN. La razón es de lectura: así la tabla “principal”, la que no pierde filas, siempre está en el FROM, arriba del todo. Si mezclas LEFT y RIGHT en una consulta con cuatro tablas, nadie sabe a simple vista qué filas se conservan.

Nota: SQLite no admitía RIGHT JOIN ni FULL OUTER JOIN hasta la versión 3.39 (2022). MySQL, MariaDB y PostgreSQL tienen RIGHT JOIN desde siempre.

FULL OUTER JOIN

FULL OUTER JOIN conserva todas las filas de las dos tablas. Donde hay pareja, las junta; donde no, rellena con NULL.

                   solo en A   en A y en B   solo en B
FULL OUTER JOIN       ███         ███           ███

SELECT e.nombre, d.nombre AS departamento
FROM empleados e
FULL OUTER JOIN departamentos d ON e.departamento_id = d.id;

nombre	departamento
Carmen	Dirección
Jorge	Ventas
Lucía	Ventas
Iker	Almacén
Nerea	Almacén
Raúl	NULL
NULL	Marketing

Aparecen a la vez Raúl (empleado sin departamento) y Marketing (departamento sin empleados). Es útil para comparar dos listas y ver qué sobra en cada lado, por ejemplo el inventario del sistema frente al recuento real del almacén.

Cómo emularlo en MySQL

MySQL y MariaDB no tienen FULL OUTER JOIN. PostgreSQL, SQL Server y SQLite (desde 3.39) sí. En MySQL se consigue juntando dos consultas con UNION:

-- 1) Todos los empleados, con su departamento si lo tienen
SELECT e.nombre, d.nombre AS departamento
FROM empleados e
LEFT JOIN departamentos d ON e.departamento_id = d.id

UNION ALL

-- 2) Solo los departamentos que no tienen ningún empleado
SELECT e.nombre, d.nombre
FROM departamentos d
LEFT JOIN empleados e ON e.departamento_id = d.id
WHERE e.id IS NULL;

El resultado es la misma tabla de siete filas de arriba. La primera parte trae el lado izquierdo completo; la segunda añade lo que faltaba del derecho. El WHERE e.id IS NULL evita repetir las filas que ya salieron en la primera parte.

Consejo: también verás esta versión: LEFT JOIN ... UNION ... RIGHT JOIN .... Funciona porque UNION (sin ALL) elimina duplicados, pero si hay filas repetidas legítimas en los datos también las fusionaría. La versión con UNION ALL y IS NULL es más segura.

CROSS JOIN: todas las combinaciones

CROSS JOIN no lleva ON: empareja cada fila de A con cada fila de B. Es el producto cartesiano. Si A tiene 3 filas y B tiene 2, el resultado tiene 3 × 2 = 6.

CROSS JOIN
A = {S, M, L}   B = {negro, blanco}
→ S-negro, S-blanco, M-negro, M-blanco, L-negro, L-blanco

Un uso real: una tienda de camisetas quiere generar todas las variantes de un producto.

CREATE TABLE tallas (talla VARCHAR(3));
CREATE TABLE colores (color VARCHAR(10));
INSERT INTO tallas VALUES ('S'), ('M'), ('L');
INSERT INTO colores VALUES ('negro'), ('blanco');

SELECT t.talla, c.color
FROM tallas t
CROSS JOIN colores c
ORDER BY t.talla, c.color;

talla	color
L	blanco
L	negro
M	blanco
M	negro
S	blanco
S	negro

(El orden es alfabético: L va antes que M y S).

Otros usos: crear un calendario de turnos (empleados × días), o una tabla de todas las parejas posibles de un torneo.

El CROSS JOIN accidental

Si escribes las tablas separadas por comas y olvidas la condición, obtienes un CROSS JOIN sin querer:

SELECT COUNT(*) FROM clientes, pedidos;

COUNT(*)
25

Cinco clientes por cinco pedidos: 25 filas sin sentido. Con tablas de 10 000 filas serían 100 millones. Por eso es mejor escribir siempre JOIN ... ON: la condición queda pegada a cada tabla y es difícil olvidarla. Y cuando de verdad quieras todas las combinaciones, escribe CROSS JOIN para que quede claro que es intencionado.

Cuidado: PostgreSQL da error si escribes JOIN sin ON, pero MySQL y SQLite lo aceptan y lo tratan como un CROSS JOIN. No confíes en que el gestor te avise.

Self join: una tabla consigo misma

Un self join une una tabla con ella misma. Se usa cuando una fila apunta a otra fila de la misma tabla, como jefe_id en empleados. El truco es usar dos alias distintos, como si fueran dos copias: e para el empleado y j para el jefe.

SELECT e.nombre AS empleado, j.nombre AS jefe
FROM empleados e
JOIN empleados j ON e.jefe_id = j.id;

empleado	jefe
Jorge	Carmen
Lucía	Jorge
Iker	Carmen
Nerea	Iker
Raúl	Carmen

Carmen no aparece como empleada porque no tiene jefe (jefe_id es NULL). Para incluirla, usa LEFT JOIN:

SELECT e.nombre AS empleado, COALESCE(j.nombre, '(sin jefe)') AS jefe
FROM empleados e
LEFT JOIN empleados j ON e.jefe_id = j.id;

empleado	jefe
Carmen	(sin jefe)
Jorge	Carmen
Lucía	Jorge
Iker	Carmen
Nerea	Iker
Raúl	Carmen

Como tienes las dos “copias” en la misma fila, puedes compararlas. ¿Quién cobra más que su jefe?

SELECT e.nombre AS empleado, e.salario, j.nombre AS jefe, j.salario AS salario_jefe
FROM empleados e
JOIN empleados j ON e.jefe_id = j.id
WHERE e.salario > j.salario;

empleado	salario	jefe	salario_jefe
Lucía	2150.00	Jorge	2100.00

Y combinado con GROUP BY, cuántas personas tiene a su cargo cada jefe:

SELECT j.nombre AS jefe, COUNT(*) AS a_su_cargo
FROM empleados e
JOIN empleados j ON e.jefe_id = j.id
GROUP BY j.id, j.nombre
ORDER BY a_su_cargo DESC;

jefe	a_su_cargo
Carmen	3
Jorge	1
Iker	1

Otros casos típicos de self join: categorías con subcategorías, comentarios que responden a otros comentarios o usuarios que siguen a otros usuarios.

JOIN con condiciones que no son igualdad

El ON admite cualquier condición, no solo =. Imagina una tabla de tramos de descuento según el importe del pedido:

CREATE TABLE tramos (
  nombre VARCHAR(20),
  minimo DECIMAL(10, 2),
  maximo DECIMAL(10, 2),
  descuento INT
);
INSERT INTO tramos VALUES
  ('Bronce', 0, 30, 0), ('Plata', 30, 100, 5), ('Oro', 100, 1000000, 10);

SELECT p.id, p.importe, t.nombre AS tramo, t.descuento
FROM pedidos p
JOIN tramos t ON p.importe >= t.minimo AND p.importe < t.maximo
ORDER BY p.id;

id	importe	tramo	descuento
1	45.00	Plata	5
2	120.50	Oro	10
3	12.99	Bronce	0
4	60.00	Plata	5
5	33.25	Plata	5

Cada pedido se empareja con el tramo en el que cae su importe. Fíjate en que el límite inferior usa >= y el superior <: así un pedido de 30,00 € cae solo en Plata y nunca en dos tramos a la vez.

Semi-join y anti-join con EXISTS

A veces no quieres combinar tablas, sino solo preguntar si algo existe en la otra. Para eso hay dos patrones con nombre propio:

• Semi-join: filas de A que tienen al menos una pareja en B.
• Anti-join: filas de A que no tienen ninguna pareja en B.

Se escriben con EXISTS y NOT EXISTS, que verás a fondo en subconsultas.

-- Semi-join: clientes que han hecho algún pedido
SELECT c.nombre
FROM clientes c
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

nombre
Ana García
Luis Pérez
Marta Ruiz
Eva Martín

Compáralo con un JOIN normal: Ana saldría dos veces, porque tiene dos pedidos. EXISTS solo pregunta “¿hay alguno?”, así que cada cliente aparece una vez como mucho.

-- Anti-join: clientes que nunca han comprado
SELECT c.nombre
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

nombre
Pablo Díaz

Es lo mismo que el LEFT JOIN ... WHERE p.id IS NULL de la lección anterior, pero dice mejor lo que buscas.

Cuidado con…

• Filtrar en WHERE la tabla opcional de un LEFT JOIN. Esto convierte el LEFT JOIN en un INNER JOIN sin que te des cuenta:

SELECT c.nombre, p.importe
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE p.importe > 50;

nombre	importe
Marta Ruiz	120.50
Luis Pérez	60.00

Los clientes sin pedidos tienen importe NULL, y NULL > 50 no es verdadero, así que desaparecen. Si quieres todos los clientes y solo los pedidos grandes, pon la condición en el ON:

SELECT c.nombre, p.importe
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id AND p.importe > 50;

nombre	importe
Ana García	NULL
Luis Pérez	60.00
Marta Ruiz	120.50
Pablo Díaz	NULL
Eva Martín	NULL

• Olvidar los alias en un self join. FROM empleados JOIN empleados da error: la base de datos no sabe a qué “copia” te refieres.
• Filas duplicadas. Si una fila de A tiene varias parejas en B, sale varias veces. Si solo querías saber si existe, usa EXISTS.
• Usar FULL OUTER JOIN en MySQL. Da error de sintaxis. Usa la emulación con UNION ALL.

Resumen

Tipo	Qué devuelve	Uso típico
INNER JOIN	Solo filas con pareja en ambas tablas	Pedidos con los datos de su cliente
LEFT JOIN	Todo A + pareja de B (o NULL)	Clientes con o sin pedidos
RIGHT JOIN	Todo B + pareja de A (o NULL)	Se reescribe como LEFT JOIN
FULL OUTER JOIN	Todo A + todo B	Comparar dos listas (no existe en MySQL)
CROSS JOIN	Cada fila de A con cada fila de B	Combinaciones: tallas × colores
Self join	Una tabla unida consigo misma	Empleados y jefes
Semi-join (EXISTS)	Filas de A con alguna pareja en B, sin duplicar	Clientes que han comprado
Anti-join (NOT EXISTS)	Filas de A sin pareja en B	Clientes que nunca han comprado

En la siguiente lección, subconsultas, aprenderás a meter una consulta dentro de otra, que es la base de EXISTS.

SELECT t.talla, c.color
FROM tallas t
CROSS JOIN colores c;

SELECT c.nombre, p.importe
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE p.importe > 50;