SELF JOIN EN MYSQL: GUÍA COMPLETA Y PRÁCTICA

Un Self JOIN consiste en hacer referencia a la misma tabla más de una vez en una consulta, asignándole alias diferentes para poder diferenciarlas. Sintaxis básica:

SELECT 
t1.columna1,
t2.columna2
FROM tabla AS t1
JOIN tabla AS t2 ON t1.columna_relacion = t2.columna_relacion
WHERE ...;
Se puede usar "INNER JOIN", "LEFT JOIN", "RIGHT JOIN" o incluso "CROSS JOIN" según el caso.

Ejemplo clásico

Buscar empleados y sus jefes: Se tiene una tabla empleados donde cada empleado tiene un "jefe_id" que referencia al id de otro empleado de la misma tabla: 

CREATE TABLE empleados (
id INT PRIMARY KEY,
nombre VARCHAR(100),
jefe_id INT,
salario DECIMAL(10,2)
);

INSERT INTO empleados VALUES
(1, 'Carlos López', NULL, 8000),
(2, 'María García', 1, 6500),
(3, 'Juan Pérez', 1, 6200),
(4, 'Ana Martínez', 2, 5500),
(5, 'Luis Rodríguez', 2, 5800);
Consulta "Self JOIN":

SELECT 
e.nombre AS empleado,
j.nombre AS jefe,
e.salario AS salario_empleado
FROM empleados e
LEFT JOIN empleados j ON e.jefe_id = j.id
ORDER BY e.nombre;

Resultado esperado

Empleado Jefe Salario Empleado
Ana Martínez María García 5500.00
Carlos López NULL 8000.00
Juan Pérez Carlos López 6200.00
Luis Rodríguez María García 5800.00
María García Carlos López 6500.00
Se usa "LEFT JOIN" para incluir al empleado que no tiene jefe porque es el director general.

Otros casos de uso comunes

Encontrar duplicados o registros similares

-- Encontrar productos con el mismo precio
SELECT 
p1.nombre AS producto1,
p2.nombre AS producto2,
p1.precio
FROM productos p1
JOIN productos p2 ON p1.precio = p2.precio 
AND p1.id < p2.id;  -- Evita duplicados (p1 con p2 y p2 con p1)

Comparación de fechas o periodos

-- Comparar ventas del mes actual con el mes anterior
SELECT 
v1.mes,
v1.total AS total_actual,
v2.total AS total_mes_anterior
FROM ventas_mensuales v1
LEFT JOIN ventas_mensuales v2 
ON v1.mes = DATE_ADD(v2.mes, INTERVAL 1 MONTH);

Árbol jerárquico (categorías, comentarios, etc.)

-- Categorías padre-hijo
SELECT 
c.nombre AS categoria,
p.nombre AS categoria_padre
FROM categorias c
LEFT JOIN categorias p ON c.padre_id = p.id;

Ejemplo con CTE recursivo (MySQL 8.0+)

WITH RECURSIVE jerarquia AS (
SELECT id, nombre, jefe_id, 0 AS nivel
FROM empleados
WHERE jefe_id IS NULL
UNION ALL
SELECT e.id, e.nombre, e.jefe_id, j.nivel + 1
FROM empleados e
JOIN jerarquia j ON e.jefe_id = j.id
)
SELECT * FROM jerarquia;

Consideraciones de Rendimiento

  • Los índices son fundamentales: Asegurarse de tener índices en las columnas donde se usan en la condición "ON".
  • Evita Cartesian products: Siempre incluye una condición en el "ON", salvo que realmente se necesite un "CROSS JOIN".
  • El uso de "Alias" es obligatorios: Sin "Alias" la consulta fallará o será confusa.
  • "Self JOIN" y "GROUP BY": Es muy potente para análisis, pero puede ser costoso en tablas grandes.