Las Common Table Expressions (CTE), conocidas en SQL por la palabra clave WITH, son una de las herramientas más potentes y elegantes de las bases de datos modernas. Técnicamente, una CTE es un conjunto de resultados temporal que existe solo durante la ejecución de una sola consulta (SELECT, INSERT, UPDATE o DELETE).

WITH mi_tabla_temporal AS (
    -- Aquí va una consulta compleja
    SELECT columna1, columna2 FROM tabla_real
)
SELECT * FROM mi_tabla_temporal; -- Aquí usamos la CTE

En general las CTEs se prefieren por tres razones:

1. Legibilidad (El factor “Clean Code”): Las subconsultas se leen de adentro hacia afuera, lo cual es confuso. Las CTEs se leen de arriba hacia abajo, como una historia.
2. Modularidad: Puedes definir varias CTEs separadas por comas y luego unirlas todas en el SELECT final.
3. Recursividad: Las CTEs son la única forma en SQL de realizar consultas recursivas (por ejemplo, para recorrer organigramas o estructuras de árbol).

Ejemplo Práctico con el Conjunto Iris

Se presenta una modificación del dataset “Iris Species” al siguiente esquema relacional:

¿Cómo puedo comparar cada flor individual con el promedio de su propia especie?

Con una CTE, el proceso es sencillo:

WITH promedios_especie AS (
    -- Paso 1: Calculamos los promedios por especie una sola vez
    SELECT 
        especie_id, 
        AVG(petal_length) AS promedio_pétalo_especie
    FROM iris_muestras
    GROUP BY especie_id
)
-- Paso 2: Unimos la tabla original con nuestra "tabla temporal" (CTE)
SELECT 
    m.id_muestra,
    e.nombre_especie,
    m.petal_length AS medida_real,
    p.promedio_pétalo_especie,
    -- Calculamos la diferencia
    ROUND(m.petal_length - p.promedio_pétalo_especie, 2) AS desviacion_del_promedio
FROM 
    iris_muestras AS m
JOIN 
    especies_detalles AS e ON m.especie_id = e.especie_id
JOIN 
    promedios_especie AS p ON m.especie_id = p.especie_id
ORDER BY 
    desviacion_del_promedio DESC;

• Creamos un “mini-reporte” de promedios llamado promedios_especie.
• En la consulta principal, tratamos a promedios_especie como si fuera una tabla real.
• El código es extremadamente fácil de depurar: si el promedio está mal, solo revisas lo que hay dentro del WITH.

CTE vs. Subconsulta: Comparativa Visual