SQL SELF JOIN: unir una tabla consigo misma

Lección 5.7 · Tiempo de lectura: ~5 min · Tareas de práctica ↓

Un self join en SQL une una tabla consigo misma: la misma tabla aparece dos veces en la consulta con dos alias distintos. En esta lección aprenderás por qué los alias son obligatorios, cómo encontrar pares de filas que comparten un valor sin repeticiones y cómo un self join lee una jerarquía empleado–jefe.

A veces las filas que quieres relacionar están en la misma tabla: un empleado y su jefe, dos actores con el mismo apellido, un vuelo y su vuelo de conexión. No existe una palabra clave SELF JOIN. Es un INNER JOIN o LEFT JOIN común en el que ambos lados son la misma tabla.

Los alias son obligatorios

La base de datos tiene que saber de qué copia de la tabla viene cada columna, así que cada copia necesita su propio alias:

SELECT a1.column, a2.column
FROM some_table AS a1
INNER JOIN some_table AS a2 ON a1.some_column = a2.some_column;

Piensa en a1 y a2 como dos tablas separadas con el mismo contenido.

Encontrar pares de filas

¿Qué actores comparten apellido?

SELECT a1.first_name AS first_actor,
       a2.first_name AS second_actor,
       a1.last_name
FROM actor AS a1
INNER JOIN actor AS a2 ON a2.last_name = a1.last_name
                      AND a2.actor_id > a1.actor_id
ORDER BY a1.last_name, first_actor, second_actor;

Resultado: 108 pares de actores con el mismo apellido, cada par una sola vez.

La condición a2.actor_id > a1.actor_id cumple dos funciones:

• excluye que un actor coincida consigo mismo, ya que cada actor tiene el mismo apellido que él mismo;
• conserva solo uno de los dos pares espejo: con <> obtendrías tanto "CHRISTIAN–DEBBIE AKROYD" como "DEBBIE–CHRISTIAN AKROYD".

Comparar filas

Un self join también puede comparar una fila con otras filas de la misma tabla. Películas más largas que "ACADEMY DINOSAUR" y con la misma clasificación:

SELECT f2.title, f2.rating, f2.length
FROM film AS f1
INNER JOIN film AS f2 ON f2.rating = f1.rating
                     AND f2.length > f1.length
WHERE f1.title = 'ACADEMY DINOSAUR'
ORDER BY f2.length DESC
LIMIT 10;

Resultado: las 10 películas PG más largas que duran más que "ACADEMY DINOSAUR" (86 minutos).

Jerarquías: empleados y jefes

El self join clásico es una jerarquía guardada en una sola tabla: cada fila de empleado tiene un manager_id que apunta a otra fila de la misma tabla. Sakila no tiene una tabla así, así que este es el patrón:

SELECT e.name AS employee,
       m.name AS manager
FROM employee AS e
LEFT JOIN employee AS m ON m.employee_id = e.manager_id;

• e es la copia que cumple el papel de empleados, m el de jefes.
• LEFT JOIN conserva al jefe máximo, cuyo manager_id es NULL; con INNER JOIN esa fila desaparecería.

Un self join sube un nivel. Para recorrer toda la jerarquía a cualquier profundidad, usarás CTE recursivos en la lección 6.5.

Conclusiones clave:

• Un self join es un join normal en el que ambos lados son la misma tabla.
• Cada copia de la tabla necesita su propio alias.
• Usa a1.id < a2.id para excluir autocoincidencias y pares espejo repetidos.
• Para jerarquías, une manager_id con la clave de la misma tabla; usa LEFT JOIN para conservar el nivel superior.

Preguntas frecuentes

¿Qué es un self join en SQL?

Un join de una tabla consigo misma. La tabla aparece dos veces con alias distintos, y la condición de unión conecta las filas de una copia con las de la otra, por ejemplo un empleado con su jefe.

¿Cómo evito pares duplicados en un self join?

Compara las claves con < en lugar de <>: a1.id < a2.id. Así se excluye que una fila coincida consigo misma y se conserva solo uno de los dos pares espejo.

¿SELF JOIN es una palabra clave SQL aparte?

No. Un self join se escribe con INNER JOIN o LEFT JOIN; la única diferencia es que las dos tablas del join son la misma.