¿Qué es un auto-join (self-join)?

Question

Accepted Answer

Un **self-join** (auto-join) es una unión donde una tabla se une **consigo misma** — útil cuando las filas de una tabla se relacionan con *otras filas de la misma tabla*, como jerarquías (empleados y sus gerentes) o comparar filas dentro de una tabla.

## El ejemplo clásico: empleados y gerentes

```sql
-- join employees to itself to pair each employee with their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;   -- same table, two aliases!
-- → Bob | Ann, Carol | Ann, Ann | NULL
```

La clave son los **alias de tabla** (`e` y `m`) — te permiten tratar la tabla única como dos "copias" para poder unir la fila del empleado con su fila de gerente dentro de la misma tabla. (Usar LEFT JOIN mantiene los empleados sin gerente.)

## Otros usos de self-joins

```sql
-- find pairs of products with the same price
SELECT a.name, b.name, a.price
FROM products a
JOIN products b ON a.price = b.price AND a.id < b.id;   -- a.id < b.id avoids duplicates/self-pairs

-- find rows related to other rows in the same table (sequences, comparisons)
```

Los self-joins también se usan para comparar filas dentro de una tabla (encontrar duplicados, pares o entradas relacionadas) — la condición `a.id < b.id` es un truco común para evitar emparejar una fila consigo misma y evitar pares duplicados.

## Cómo funciona conceptualmente

```text
A self-join is just a regular join where both sides happen to be the SAME table.
The aliases create two logical "instances" of the table, so each row of one instance
can be matched against rows of the other instance.
→ Nothing special syntactically — it's a normal join, with the trick being aliases.
```

## Por qué es importante

Los self-joins son una técnica SQL útil para manejar **relaciones dentro de una sola tabla** — particularmente datos jerárquicos o auto-referenciados, que es un patrón común en el mundo real, por lo que entenderlos es valioso para estos escenarios.

El uso clásico y más importante es **jerarquías** donde las filas hacen referencia a otras filas en la misma tabla (empleados haciendo referencia a sus gerentes, categorías con categorías padre, estructuras organizacionales, comentarios en cadena) — los self-joins te permiten consultar estas relaciones (emparejando cada empleado con su gerente) lo cual consultas más simples no pueden expresar.

Entender que el mecanismo clave son los **alias de tabla** (creando dos "instancias" lógicas de la tabla para que las filas puedan compararse entre sí dentro de la misma tabla) desmitifica la técnica — es simplemente una unión normal donde ambos lados son la misma tabla.

Los self-joins también manejan la comparación de filas dentro de una tabla (encontrar pares, duplicados o entradas relacionadas, frecuentemente con una condición como `a.id < b.id` para evitar auto-emparejamiento y duplicados).

Como los datos auto-referenciados y jerárquicos son comunes (organigramas, árboles de categorías, relaciones de gerentes, comparación de registros dentro de una tabla), y como los self-joins son la forma estándar de consultar tales relaciones dentro de una sola tabla, entender los self-joins — que una tabla puede unirse consigo misma usando alias, y los patrones comunes (jerarquías, comparaciones de filas) — es conocimiento SQL práctico y relevante para manejar una clase de consultas que involucran relaciones intra-tabla que surgen frecuentemente en modelos de datos reales, y un tema común en entrevistas que demuestra una comprensión más profunda de las uniones.