Self Join

Un self join es un JOIN donde una tabla se une consigo misma. No es un tipo de JOIN nuevo (se usa INNER JOIN o LEFT JOIN como siempre), sino una técnica: la misma tabla aparece dos veces en la consulta, con alias diferentes, como si fueran dos tablas distintas.

Los self joins son imprescindibles para trabajar con datos jerárquicos (empleados y supervisores, categorías y subcategorías) y para comparar filas dentro de la misma tabla (productos de la misma categoría, pedidos del mismo cliente).

Por qué son necesarios los alias

En un self join, la misma tabla se usa dos veces. Sin alias, MySQL no podría distinguir a cuál de las dos "copias" te refieres al nombrar las columnas:

-- ERROR: ambigüedad
SELECT nombre, nombre
FROM empleados
JOIN empleados ON supervisor_id = id;
 
-- CORRECTO: alias distintos
SELECT e.nombre, s.nombre
FROM empleados e
JOIN empleados s ON e.supervisor_id = s.id;

Los alias son obligatorios en un self join. En el ejemplo, e representa al empleado y s a su supervisor. Aunque es la misma tabla física, MySQL los trata como dos tablas lógicas separadas.

Empleados y supervisores

El caso clásico de self join. La tabla empleados tiene una columna supervisor_id que apunta al id de otro empleado en la misma tabla. Para ver cada empleado junto al nombre de su supervisor, necesitamos unir la tabla consigo misma:

SELECT
    e.nombre AS empleado,
    e.puesto,
    s.nombre AS supervisor,
    s.puesto AS puesto_supervisor
FROM empleados e
INNER JOIN empleados s ON e.supervisor_id = s.id
ORDER BY s.nombre, e.nombre;

La tabla empleados aparece dos veces: como e (el empleado) y como s (el supervisor). La condición e.supervisor_id = s.id conecta cada empleado con su jefe directo. Ricardo no aparece porque su supervisor_id es NULL y el INNER JOIN descarta las filas sin correspondencia.

Podemos ver la estructura: Ricardo dirige a Sofía y Alberto. Sofía supervisa a Natalia, Daniel y Patricia. Natalia supervisa a Raúl. Alberto supervisa a Cristina. Cristina supervisa a Marcos e Inés.

Incluir al director general

Para que Ricardo (que no tiene supervisor) también aparezca, usamos LEFT JOIN:

SELECT
    e.nombre AS empleado,
    e.puesto,
    COALESCE(s.nombre, '-') AS supervisor
FROM empleados e
LEFT JOIN empleados s ON e.supervisor_id = s.id
ORDER BY e.nombre;

Ahora los 10 empleados aparecen. Ricardo muestra "-" como supervisor gracias al COALESCE que sustituye NULL por un valor más legible.

Niveles de jerarquía

Podemos ir más allá y mostrar hasta dos niveles de supervisión encadenando self joins:

SELECT
    e.nombre AS empleado,
    s1.nombre AS supervisor_directo,
    s2.nombre AS supervisor_superior
FROM empleados e
LEFT JOIN empleados s1 ON e.supervisor_id = s1.id
LEFT JOIN empleados s2 ON s1.supervisor_id = s2.id
ORDER BY e.nombre;

Cada LEFT JOIN añade un nivel de jerarquía. Raúl reporta a Natalia (supervisor directo), quien reporta a Sofía (supervisor superior). Ricardo y Sofía muestran NULL en supervisor superior porque están en la cima de la cadena.

Este enfoque tiene un límite: necesitas un JOIN por cada nivel de profundidad. Para jerarquías de profundidad variable o desconocida, las CTEs recursivas (que veremos más adelante) son más apropiadas.

Jerarquía de categorías

La tabla categorias tiene una estructura similar con categoria_padre_id. Podemos usarla para ver cada subcategoría junto a su categoría padre:

SELECT
    sub.nombre AS subcategoria,
    padre.nombre AS categoria_padre
FROM categorias sub
INNER JOIN categorias padre ON sub.categoria_padre_id = padre.id
ORDER BY padre.nombre, sub.nombre;

Las 11 subcategorías aparecen junto a su categoría padre. Electrónica tiene 3 subcategorías (Accesorios, Portátiles, Smartphones), mientras que Deportes, Hogar, Libros y Ropa tienen 2 cada una. Las 5 categorías principales no aparecen como subcategorías porque su categoria_padre_id es NULL.

Comparar salarios con el supervisor

Un self join permite comparar datos entre filas relacionadas de la misma tabla. Veamos la diferencia salarial entre cada empleado y su supervisor:

SELECT
    e.nombre AS empleado,
    e.salario,
    s.nombre AS supervisor,
    s.salario AS salario_supervisor,
    s.salario - e.salario AS diferencia
FROM empleados e
INNER JOIN empleados s ON e.supervisor_id = s.id
ORDER BY diferencia DESC;

Alberto, Daniel y Patricia tienen la mayor diferencia salarial con sus supervisores (23.000 euros). Marcos e Inés tienen la menor (9.000 euros). Este tipo de análisis sería imposible sin un self join, ya que la información del empleado y de su supervisor están en la misma tabla.

Productos de la misma categoría

Podemos encontrar pares de productos dentro de una misma categoría para comparar precios:

SELECT
    p1.nombre AS producto,
    p2.nombre AS otro_producto,
    p1.precio,
    p2.precio AS otro_precio,
    ABS(p1.precio - p2.precio) AS diferencia
FROM productos p1
INNER JOIN productos p2 ON p1.categoria_id = p2.categoria_id
    AND p1.id < p2.id
WHERE p1.categoria_id = 6
ORDER BY diferencia DESC;

La condición p1.id < p2.id es crucial: evita que un producto se compare consigo mismo y evita pares duplicados (sin ella, veríamos tanto "iPhone vs Samsung" como "Samsung vs iPhone"). La categoría 6 (Smartphones) tiene 4 productos, lo que genera 6 pares posibles (4 × 3 / 2).

El iPhone 15 Pro y el Xiaomi 14 tienen la mayor diferencia de precio dentro de la categoría: 700 euros.

Resumen

El self join no es un tipo de JOIN diferente, sino una técnica que aplica JOINs normales (INNER JOIN, LEFT JOIN) sobre la misma tabla dos o más veces, usando alias distintos. Es imprescindible para datos jerárquicos (empleados, categorías, comentarios con respuestas) y para comparar filas dentro de la misma tabla.

Practica con Self Join

Usa el editor para unir una tabla consigo misma:

En el siguiente artículo veremos NATURAL JOIN, un tipo de unión que intenta adivinar la condición automáticamente, y por qué es mejor evitarlo.