Explique qué son las expresiones de tabla comunes recursivas (CTE) y cómo se utilizan en las consultas SQL

Las expresiones de tabla común recursivas (CTE) son un tipo especial de conjuntos de resultados temporales en SQL que pueden hacer referencia a sí mismos para realizar consultas recursivas. Son útiles para trabajar con estructuras de datos jerárquicas o gráficas, por ejemplo, para recorrer un árbol de categorías, organizar empleados por subordinación, etc.

Un CTE recursivo consta de dos partes:

1. Consulta base — conjunto inicial de filas.
2. Consulta recursiva — utiliza los resultados de la iteración anterior para obtener el siguiente nivel de datos.

Ejemplo: obtener todos los empleados subordinados con id = 1 en una jerarquía de empleados.

WITH RECURSIVE Subordinates AS (
    SELECT id, manager_id, name
    FROM employees
    WHERE id = 1  -- caso base
  
    UNION ALL
  
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    INNER JOIN Subordinates s ON e.manager_id = s.id  -- caso recursivo
)
SELECT * FROM Subordinates;

Este consulta primero selecciona al empleado con id=1, luego recursivamente añade a todos los empleados cuyo manager_id coincide con el id de los empleados ya encontrados, hasta recorrer toda la jerarquía.