SQL Academy · Lección

Limitaciones de los self joins

Cuándo necesita recurrir a la recursividad

Lección 4 de 413 pasos

Limitaciones de los self joins es una lección gratuita de SQL Academy en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Academy incluye 4 lecciones en total.

¿Qué es una autounión?

Una autounión consiste en unir una tabla consigo misma. Resulta útil para comparar filas dentro de la misma tabla, por ejemplo, para encontrar empleados y sus gerentes almacenados en una única tabla employees.

Antes de explorar sus limitaciones, recordemos cómo funciona en la práctica una autounión básica.

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

Un nivel de profundidad

Una autounión resuelve elegantemente un salto en una jerarquía. Si desea emparejar cada empleado con su gerente directo, solo necesita una autounión.

Esto funciona perfectamente cuando sus datos tienen un solo nivel de profundidad o cuando solo le interesan las relaciones directas entre padres e hijos.

SELECT child.name AS employee, parent.name AS direct_manager
FROM employees child
LEFT JOIN employees parent ON child.manager_id = parent.id;

Dos niveles: ya se complica

¿Qué ocurre si necesita obtener los empleados, sus gerentes y los gerentes de estos? Debe añadir una segunda autounión. La consulta crece y se vuelve más difícil de leer.

Cada nivel adicional de la jerarquía requiere un alias de unión más y una cláusula JOIN más.

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

Tres niveles: el patrón se desmorona

Añadir un tercer nivel obliga a incorporar otra unión. A estas alturas, la consulta es prolija, frágil y difícil de mantener. Si cambia la profundidad de la jerarquía, debe reescribir la consulta completa.

Esta es la primera gran limitación de las autouniones: no escalan con la profundidad.

SELECT e.name AS employee,
       m.name AS manager,
       gm.name AS grand_manager,
       ggm.name AS great_grand_manager
FROM employees e
LEFT JOIN employees m   ON e.manager_id = m.id
LEFT JOIN employees gm  ON m.manager_id = gm.id
LEFT JOIN employees ggm ON gm.manager_id = ggm.id;

Profundidad desconocida: las autouniones no sirven

En organigramas o árboles de categorías del mundo real, la profundidad suele ser desconocida en el momento de ejecutar la consulta. Las autouniones le obligan a fijar de antemano el número de niveles. Si mañana la jerarquía tiene 10 niveles de profundidad, su consulta con una autounión de 3 niveles omitirá datos silenciosamente.

Esta es una limitación fundamental: las autouniones no pueden recorrer un número arbitrario de niveles.

-- This only retrieves up to 3 levels deep.
-- Employees deeper than level 3 are simply missing from results.
SELECT e.name, m.name, gm.name
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;

Los ciclos inutilizan por completo las autouniones

Otra limitación grave: si los datos contienen un ciclo (A gestiona a B, B gestiona a C y C gestiona a A), una consulta con una autounión no entra en un bucle infinito, pero tampoco detecta ni informa correctamente del ciclo.

No puede protegerse contra las referencias circulares usando únicamente autouniones. Las consultas recursivas cuentan con mecanismos integrados de detección de ciclos de los que las autouniones carecen por completo.

-- Cyclic data: row 3 points back to row 1
-- id | name    | manager_id
--  1 | Alice   | 3   <-- cycle!
--  2 | Bob     | 1
--  3 | Charlie | 2

-- A self join just shows one hop; it cannot detect the loop
SELECT e.name, m.name AS reports_to
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Introducción a las CTE recursivas

SQL proporciona una solución diseñada específicamente para recorrer jerarquías de profundidad desconocida: la expresión de tabla común (CTE) recursiva. Utiliza la sintaxis WITH RECURSIVE, compatible con PostgreSQL, MySQL 8+, SQLite y SQL Server.

Una CTE recursiva tiene dos partes: un miembro ancla (las filas iniciales) y un miembro recursivo (el paso que sigue cada relación).

WITH RECURSIVE org_tree AS (
  -- Anchor: start with the top-level CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find each employee whose manager is already in org_tree
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, depth FROM org_tree ORDER BY depth;

Seguimiento de la ruta completa

Una característica muy útil de las CTE recursivas es que permiten acumular contexto a medida que desciende por la jerarquía. Por ejemplo, puede construir la ruta completa desde la raíz hasta cada nodo, algo completamente imposible con una autounión estática.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id,
         name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id,
         ot.path || ' > ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

Autounión frente a CTE recursiva: cuándo elegir

Use una autounión cuando:

Necesite exactamente uno o dos niveles de jerarquía.
La profundidad sea fija y conocida de antemano.
Busque sencillez y no quiera asumir la sobrecarga de una CTE.

Use una CTE recursiva cuando:

La profundidad sea variable o desconocida.
Necesite la ruta completa de ascendencia o descendencia.
Quiera detectar ciclos mediante la cláusula CYCLE o mediante controles manuales.

Consideraciones de rendimiento

Las autouniones sobre columnas indexadas son extremadamente rápidas en consultas de profundidad fija. Cada unión es una única búsqueda y el optimizador de la base de datos las gestiona eficazmente.

Las CTE recursivas son más flexibles, pero pueden resultar costosas en árboles profundos o con mucha ramificación. Añada siempre un control de límite de profundidad en el miembro recursivo para evitar consultas descontroladas provocadas por datos incorrectos o ciclos inesperados.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10   -- safety guard: stop at depth 10
)
SELECT name, depth FROM org_tree;

Casos de uso reales que requieren recursión

Muchos modelos de datos habituales requieren recorrer una profundidad arbitraria, algo que las autouniones sencillamente no pueden hacer:

Árboles de categorías: categorías de productos anidadas en un catálogo de comercio electrónico.
Listas de materiales: un producto compuesto por piezas, cada una de ellas compuesta por subpiezas.
Hilos de comentarios: respuestas a respuestas a respuestas.
Rutas del sistema de archivos: directorios dentro de directorios.

En todos estos casos, opte por una CTE recursiva en lugar de apilar autouniones.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, name AS full_path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.full_path || ' / ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, full_path FROM category_tree ORDER BY full_path;

Comprobación de conocimientos

Compruebe su comprensión de las limitaciones de las autouniones y de cuándo usar CTE recursivas en su lugar.

Responde en la lección interactiva

Resumen de la lección

En esta lección ha aprendido las limitaciones de las autouniones para trabajar con datos jerárquicos:

Las autouniones funcionan bien para uno o dos niveles fijos de jerarquía.
Cada nivel adicional requiere otro JOIN explícito, lo que hace que las consultas sean frágiles y difíciles de mantener.
Las autouniones no pueden gestionar una profundidad desconocida: las filas que están más allá de los niveles fijados se excluyen silenciosamente.
No ofrecen protección contra las referencias cíclicas en los datos.
Cuando la profundidad sea variable o desconocida, use una CTE recursiva (WITH RECURSIVE).
Añada siempre un control de profundidad en las consultas recursivas para protegerse contra una ejecución descontrolada.

Saber cuándo pasar de una autounión a una CTE recursiva es una habilidad fundamental para consultar cualquier dato estructurado como un árbol en SQL.

Gratis para empezar

Aprende SQL con un tutor de IA — gratis

Escribe y ejecuta código real en tu navegador, obtén ayuda instantánea de un tutor de IA disponible 24/7 y continúa donde lo dejaste en la web o en la aplicación.

Cursos: 46
Lecciones: 183

Empezar a aprender gratis Ver el curso completo

Preguntas frecuentes

¿La lección «Limitaciones de los self joins» es gratis?

Sí — el texto completo de «Limitaciones de los self joins» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Academy, actualiza a CoddyKit PRO. El curso de SQL Academy incluye 4 lecciones en total.

¿Qué aprenderé en «Limitaciones de los self joins»?

Cuándo necesita recurrir a la recursividad Practicas SQL Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar SQL Academy?

No se requiere experiencia previa. SQL Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.

¿Cuánto tiempo toma la lección «Limitaciones de los self joins»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de SQL Academy?

Sí. Cada lección de SQL Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

← Volver a SQL Academy