Self-joins en SQL: unir una tabla consigo misma

Un self-join es un JOIN corriente en el que ambos lados hacen referencia a la misma tabla. No hay una sintaxis especial para ello: simplemente listas la tabla dos veces y le das dos alias distintos. Suena raro, pero es la herramienta de cabecera para las jerarquías (empleado → jefe), comparar filas entre sí y construir pares. Vamos a verlo sobre un esquema concreto.

A lo largo del artículo usaremos esta tabla de empleados:

CREATE TABLE employees (
    id          bigint PRIMARY KEY,
    name        text NOT NULL,
    manager_id  bigint REFERENCES employees(id),  -- points back to this table
    department  text,
    salary      numeric(10, 2),
    hired_at    date
);

Por qué unir una tabla consigo misma

La idea central: una fila de una tabla necesita «ver» otra fila de esa misma tabla. Si los jefes vivieran en una tabla managers aparte, escribirías un JOIN normal. Pero manager_id apunta de vuelta a employees, así que tienes que traer la tabla dos veces.

Aquí los alias no son un adorno opcional: son obligatorios. Sin ellos el motor no puede distinguir la «fila del empleado» de la «fila del jefe», y obtendrás un error de columna ambigua.

• e — la fila del empleado;
• m — la fila del jefe de ese empleado;
• la condición de unión enlaza e.manager_id con m.id.

La jerarquía empleado → jefe

El caso de manual es mostrar a cada empleado junto a su jefe:

SELECT e.name        AS employee,
       m.name        AS manager
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id;

Aquí hay una trampa: el CEO tiene manager_id IS NULL, y un JOIN (interno) normal descarta esa fila en silencio: NULL no coincide con nada. Para conservar a todo el mundo, incluido el que no tiene jefe, usa un LEFT JOIN:

SELECT e.name                          AS employee,
       COALESCE(m.name, '- none -')    AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY m.name NULLS FIRST, e.name;

COALESCE sustituye NULL por un marcador de posición, y NULLS FIRST hace que el CEO suba a lo más alto. Un solo self-join te da exactamente un nivel de la jerarquía. Para una profundidad arbitraria (el jefe de un jefe, y así sucesivamente) necesitas una CTE recursiva WITH RECURSIVE, pero ese es otro tema.

Comparar filas dentro de una sola tabla

Los self-joins brillan cuando necesitas comparar filas entre sí. Por ejemplo, encontrar a los empleados que ganan más que su propio jefe:

SELECT e.name   AS employee,
       e.salary AS emp_salary,
       m.name   AS manager,
       m.salary AS mgr_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary;

O comparar a cada persona con un colega del mismo departamento para ver quién fue contratado primero:

SELECT a.name AS earlier, b.name AS later, a.department
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.hired_at < b.hired_at;       -- a was hired strictly before b

Fíjate en que a.hired_at < b.hired_at cumple doble función: ordena cada par por tiempo y, a la vez, impide que una fila se una consigo misma.

Pares sin duplicados ni autocoincidencias

Si quieres cada par de empleados dentro de un departamento (por ejemplo, para emparejar revisiones de código), un JOIN ingenuo sobre a.department = b.department produce basura: cada fila coincide consigo misma (Alice–Alice) y cada par aparece dos veces (Alice–Bob y Bob–Alice).

La solución es un único predicado: comparar por id:

SELECT a.name AS person_a,
       b.name AS person_b,
       a.department
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.id < b.id          -- strict < kills both self-pairs and duplicates
ORDER BY a.department, person_a;

• a.id <> b.id eliminaría solo la autocoincidencia, pero los duplicados seguirían ahí;
• a.id < b.id elimina ambos: cada par no ordenado sobrevive exactamente una vez.

Este truco de la desigualdad estricta es el patrón más habitual a la hora de generar pares.

Cuidado. Atención al rendimiento: un self-join sobre un manager_id o department sin índice en una tabla grande puede degenerar en un Nested Loop costoso. Indexa tus columnas de unión (CREATE INDEX ON employees(manager_id)) y revisa el plan con EXPLAIN ANALYZE.

Diferencias entre bases de datos

La sintaxis del self-join es SQL estándar y se comporta igual en PostgreSQL, MySQL y ClickHouse, pero presta atención a algunos matices:

• PostgreSQL / MySQL — todo lo anterior funciona tal cual; el LEFT JOIN para conservar las filas con un padre NULL es obligatorio.
• MySQL anterior a 8.0 no tenía WITH RECURSIVE, así que las jerarquías profundas había que construirlas de forma procedimental; 8.0+ sí admite recursión.
• ClickHouse — JOIN existe pero es más costoso y menos flexible; las jerarquías suelen resolverse con funciones de array o desnormalización, y la igualdad con NULL en un JOIN se comporta de otra manera. Con grandes volúmenes de datos en ClickHouse, evita un self-join de desigualdad (a.id < b.id): paraleliza mal.

En resumen: un self-join no es más que un JOIN de una tabla consigo misma mediante alias. Recuerda tres jugadas: LEFT JOIN para jerarquías con padre NULL, comparación de columnas para analizar filas y una desigualdad estricta de id para pares sin duplicados.