Tutorial SQL: Expresiones de tabla comunes (CTE) con WITH

Sintaxis WITH

Las expresiones de tabla comunes (CTE, por sus siglas en inglés) proporcionan una forma elegante de estructurar consultas complejas en SQL. La cláusula WITH es la que nos permite definir estas expresiones temporales que existen únicamente durante la ejecución de la consulta principal.

La sintaxis básica de una expresión WITH sigue este patrón:

WITH nombre_cte [(columna1, columna2, ...)] AS (
    -- Consulta que define la CTE
    SELECT ...
)
-- Consulta principal que utiliza la CTE
SELECT * FROM nombre_cte;

Estructura básica

Una CTE se compone de tres elementos principales:

• Nombre de la CTE: Identificador único que se utilizará para referenciar los resultados.
• Lista de columnas (opcional): Si se omite, las columnas heredan los nombres de la consulta interna.
• Consulta de definición: La consulta SQL que genera los datos temporales.

Veamos un ejemplo sencillo:

WITH empleados_departamento AS (
    SELECT id_empleado, nombre, apellido, salario
    FROM empleados
    WHERE id_departamento = 3
)
SELECT nombre, apellido
FROM empleados_departamento
WHERE salario > 30000;

En este ejemplo, primero definimos una CTE llamada empleados_departamento que contiene solo los empleados del departamento 3. Luego, en la consulta principal, filtramos esos resultados para mostrar solo los empleados con salario superior a 30000.

Múltiples CTEs

Una de las ventajas principales de la sintaxis WITH es la posibilidad de definir múltiples CTEs separadas por comas:

WITH 
    dept_ventas AS (
        SELECT id_empleado 
        FROM empleados 
        WHERE id_departamento = 1
    ),
    ventas_2023 AS (
        SELECT id_empleado, SUM(monto) as total_ventas
        FROM ventas
        WHERE YEAR(fecha_venta) = 2023
        GROUP BY id_empleado
    )
SELECT e.nombre, e.apellido, v.total_ventas
FROM empleados e
JOIN dept_ventas dv ON e.id_empleado = dv.id_empleado
JOIN ventas_2023 v ON e.id_empleado = v.id_empleado
ORDER BY v.total_ventas DESC;

Este ejemplo muestra cómo podemos definir dos CTEs diferentes y luego utilizarlas juntas en la consulta principal para obtener un listado de vendedores con sus ventas totales de 2023.

Referencias entre CTEs

Una característica poderosa es que las CTEs pueden hacer referencia a otras CTEs definidas previamente en la misma cláusula WITH:

WITH 
    ventas_mensuales AS (
        SELECT 
            id_producto,
            MONTH(fecha_venta) AS mes,
            SUM(cantidad) AS unidades_vendidas
        FROM ventas
        WHERE YEAR(fecha_venta) = 2023
        GROUP BY id_producto, MONTH(fecha_venta)
    ),
    promedio_ventas AS (
        SELECT 
            id_producto,
            AVG(unidades_vendidas) AS promedio_mensual
        FROM ventas_mensuales
        GROUP BY id_producto
    )
SELECT 
    p.nombre_producto,
    pv.promedio_mensual
FROM productos p
JOIN promedio_ventas pv ON p.id_producto = pv.id_producto
ORDER BY pv.promedio_mensual DESC
LIMIT 10;

En este ejemplo, la segunda CTE (promedio_ventas) utiliza los resultados de la primera CTE (ventas_mensuales), lo que permite descomponer cálculos complejos en pasos más manejables.

Ventajas de usar WITH

La sintaxis WITH ofrece varias ventajas sobre las subconsultas tradicionales:

• Legibilidad: El código se vuelve más fácil de leer y mantener al separar lógicamente las partes de una consulta compleja.
• Reutilización: Una CTE puede referenciarse múltiples veces en la consulta principal, evitando la duplicación de código.
• Modularidad: Permite dividir consultas complejas en bloques más pequeños y manejables.

WITH 
    ingresos AS (
        SELECT id_cliente, SUM(monto) AS total_ingresos
        FROM facturas
        WHERE estado = 'pagada'
        GROUP BY id_cliente
    ),
    gastos AS (
        SELECT id_cliente, SUM(monto) AS total_gastos
        FROM compras
        GROUP BY id_cliente
    ),
    balance AS (
        SELECT 
            i.id_cliente,
            i.total_ingresos,
            COALESCE(g.total_gastos, 0) AS total_gastos,
            i.total_ingresos - COALESCE(g.total_gastos, 0) AS balance_neto
        FROM ingresos i
        LEFT JOIN gastos g ON i.id_cliente = g.id_cliente
    )
SELECT 
    c.nombre,
    c.apellido,
    b.total_ingresos,
    b.total_gastos,
    b.balance_neto
FROM clientes c
JOIN balance b ON c.id_cliente = b.id_cliente
WHERE b.balance_neto > 10000
ORDER BY b.balance_neto DESC;

Este ejemplo muestra cómo podemos usar múltiples CTEs para construir un informe financiero complejo de manera clara y estructurada.

Compatibilidad en MySQL y PostgreSQL

Tanto MySQL (desde la versión 8.0) como PostgreSQL soportan completamente la sintaxis WITH, aunque existen algunas diferencias menores:

• PostgreSQL: Soporta CTEs desde hace más tiempo y ofrece opciones adicionales como WITH RECURSIVE (para consultas recursivas) y la cláusula MATERIALIZED/NOT MATERIALIZED para controlar la optimización.

• MySQL: Incorporó soporte completo para CTEs no recursivas en la versión 8.0 y para CTEs recursivas en versiones posteriores.

-- Ejemplo compatible con MySQL 8.0+ y PostgreSQL
WITH top_productos AS (
    SELECT 
        p.id_producto,
        p.nombre_producto,
        SUM(v.cantidad) AS total_vendido
    FROM productos p
    JOIN ventas v ON p.id_producto = v.id_producto
    WHERE YEAR(v.fecha_venta) = 2023
    GROUP BY p.id_producto, p.nombre_producto
    ORDER BY total_vendido DESC
    LIMIT 5
)
SELECT 
    tp.nombre_producto,
    tp.total_vendido,
    c.nombre_categoria
FROM top_productos tp
JOIN categorias c ON tp.id_producto = c.id_producto;

Consideraciones de rendimiento

Es importante entender que las CTEs son evaluadas cada vez que se referencian en la consulta principal. Esto puede afectar al rendimiento si una CTE compleja se utiliza múltiples veces. En estos casos, considerar alternativas como tablas temporales podría ser más eficiente.

-- Ejemplo de CTE que podría causar problemas de rendimiento si se usa múltiples veces
WITH datos_complejos AS (
    SELECT 
        c.id_cliente,
        c.nombre,
        COUNT(DISTINCT o.id_orden) AS num_ordenes,
        SUM(oi.cantidad * oi.precio) AS total_gastado,
        AVG(oi.precio) AS precio_promedio
    FROM clientes c
    JOIN ordenes o ON c.id_cliente = o.id_cliente
    JOIN orden_items oi ON o.id_orden = oi.id_orden
    WHERE o.fecha_orden BETWEEN '2022-01-01' AND '2023-12-31'
    GROUP BY c.id_cliente, c.nombre
)
SELECT * FROM datos_complejos
WHERE total_gastado > 1000
UNION ALL
SELECT * FROM datos_complejos
WHERE num_ordenes > 10;

En PostgreSQL, puedes usar la cláusula MATERIALIZED para indicar que una CTE debe calcularse una sola vez:

-- PostgreSQL específico
WITH datos_complejos AS MATERIALIZED (
    -- consulta compleja aquí
)
-- consulta principal

Limitaciones

Algunas limitaciones a tener en cuenta al usar CTEs:

• No se pueden crear índices en CTEs.
• Las CTEs existen solo durante la ejecución de la consulta.
• No todas las bases de datos permiten usar CTEs en vistas, funciones o procedimientos almacenados.

En resumen, la sintaxis WITH proporciona una herramienta poderosa para estructurar consultas complejas de manera clara y modular, mejorando tanto la legibilidad como el mantenimiento del código SQL.

CTE recursivas

Las Expresiones de Tabla Comunes (CTE) recursivas representan una de las características más potentes del lenguaje SQL moderno, permitiéndonos resolver problemas que serían extremadamente difíciles o imposibles con consultas tradicionales. A diferencia de las CTE regulares, las recursivas pueden referenciarse a sí mismas, lo que nos permite trabajar con estructuras jerárquicas y generar series de datos de forma dinámica.

Fundamentos de la recursión en SQL

Una CTE recursiva consta de dos partes fundamentales:

• Caso base: La consulta inicial que proporciona el punto de partida.
• Caso recursivo: La parte que hace referencia a la propia CTE y expande los resultados.

La sintaxis general sigue este patrón:

WITH RECURSIVE nombre_cte AS (
    -- Caso base (consulta no recursiva)
    SELECT ... 
    
    UNION [ALL]
    
    -- Caso recursivo (hace referencia a nombre_cte)
    SELECT ... FROM ... JOIN nombre_cte ON ...
)
SELECT * FROM nombre_cte;

El operador RECURSIVE es obligatorio en PostgreSQL, mientras que en MySQL es opcional pero recomendado para mayor claridad.

Ejemplo: Estructura jerárquica de empleados

Uno de los casos de uso más comunes para CTE recursivas es navegar por estructuras jerárquicas, como un organigrama empresarial. Supongamos que tenemos una tabla de empleados donde cada uno tiene un jefe (excepto el director general):

WITH RECURSIVE jerarquia_empleados AS (
    -- Caso base: el director general (sin jefe)
    SELECT id_empleado, nombre, apellido, cargo, id_jefe, 1 AS nivel
    FROM empleados
    WHERE id_jefe IS NULL
    
    UNION ALL
    
    -- Caso recursivo: todos los empleados que reportan a alguien en la jerarquía
    SELECT e.id_empleado, e.nombre, e.apellido, e.cargo, e.id_jefe, j.nivel + 1
    FROM empleados e
    JOIN jerarquia_empleados j ON e.id_jefe = j.id_empleado
)
SELECT 
    nivel,
    CONCAT(REPEAT('    ', nivel-1), nombre, ' ', apellido) AS empleado,
    cargo
FROM jerarquia_empleados
ORDER BY nivel, nombre;

Este ejemplo muestra cómo construir un organigrama completo, comenzando por el director general (nivel 1) y expandiéndose hacia abajo a través de la estructura jerárquica. La función REPEAT se utiliza para crear una indentación visual que refleja el nivel jerárquico.

Generación de series numéricas

Las CTE recursivas también son excelentes para generar secuencias de datos. Veamos cómo crear una serie de números:

WITH RECURSIVE serie_numeros AS (
    -- Caso base: comenzamos con 1
    SELECT 1 AS numero
    
    UNION ALL
    
    -- Caso recursivo: añadimos 1 al número anterior hasta llegar a 10
    SELECT numero + 1
    FROM serie_numeros
    WHERE numero < 10
)
SELECT numero FROM serie_numeros;

Este ejemplo generará los números del 1 al 10. La cláusula WHERE numero < 10 es crucial ya que actúa como condición de terminación, evitando un bucle infinito.

Recorrido de rutas en grafos

Otro uso potente es encontrar rutas en estructuras de grafo, como conexiones de vuelos entre ciudades:

WITH RECURSIVE rutas_posibles AS (
    -- Caso base: vuelos directos desde Madrid
    SELECT 
        origen, 
        destino, 
        costo, 
        1 AS num_escalas,
        ARRAY[origen, destino] AS ruta
    FROM vuelos
    WHERE origen = 'Madrid'
    
    UNION ALL
    
    -- Caso recursivo: añadir conexiones adicionales
    SELECT 
        r.origen,
        v.destino,
        r.costo + v.costo AS costo_total,
        r.num_escalas + 1,
        r.ruta || v.destino AS ruta
    FROM rutas_posibles r
    JOIN vuelos v ON r.destino = v.origen
    WHERE 
        v.destino <> ALL(r.ruta) -- Evitar ciclos
        AND r.num_escalas < 3    -- Limitar a máximo 3 escalas
)
SELECT 
    origen, 
    destino, 
    costo, 
    num_escalas,
    array_to_string(ruta, ' -> ') AS ruta_completa
FROM rutas_posibles
WHERE destino = 'Tokio'
ORDER BY costo;

Este ejemplo encuentra todas las rutas posibles desde Madrid a Tokio con un máximo de 3 escalas, evitando ciclos (no visitar la misma ciudad dos veces). La función array_to_string de PostgreSQL convierte el array de ciudades en una cadena legible.

Nota: El operador <> ALL y el tipo ARRAY son específicos de PostgreSQL. En MySQL, necesitarías adaptar esta consulta usando otras técnicas.

Descomposición de intervalos de fechas

Las CTE recursivas también son útiles para descomponer intervalos de tiempo:

WITH RECURSIVE calendario AS (
    -- Caso base: fecha inicial
    SELECT 
        '2023-01-01'::DATE AS fecha
    
    UNION ALL
    
    -- Caso recursivo: añadir un día hasta llegar a la fecha final
    SELECT 
        fecha + INTERVAL '1 day'
    FROM calendario
    WHERE fecha < '2023-01-31'
)
SELECT 
    fecha,
    EXTRACT(DOW FROM fecha) AS dia_semana,
    TO_CHAR(fecha, 'Day') AS nombre_dia
FROM calendario
WHERE EXTRACT(DOW FROM fecha) IN (0, 6) -- Solo fines de semana
ORDER BY fecha;

Este ejemplo genera todas las fechas de enero de 2023 y filtra solo los fines de semana.

Consideraciones de rendimiento

Las CTE recursivas son herramientas poderosas, pero pueden consumir muchos recursos si no se controlan adecuadamente:

• Siempre incluye una condición de terminación para evitar bucles infinitos.
• Limita la profundidad de recursión cuando sea posible.
• Usa UNION en lugar de UNION ALL si necesitas eliminar duplicados.
• Considera el uso de índices en las columnas utilizadas para las condiciones de unión.

-- Ejemplo con límite de profundidad explícito
WITH RECURSIVE jerarquia_limitada AS (
    SELECT id, nombre, id_padre, 1 AS nivel
    FROM categorias
    WHERE id_padre IS NULL
    
    UNION ALL
    
    SELECT c.id, c.nombre, c.id_padre, j.nivel + 1
    FROM categorias c
    JOIN jerarquia_limitada j ON c.id_padre = j.id
    WHERE j.nivel < 5 -- Limitar a 5 niveles de profundidad
)
SELECT * FROM jerarquia_limitada;

Diferencias entre MySQL y PostgreSQL

Aunque ambos sistemas soportan CTE recursivas, existen algunas diferencias importantes:

• PostgreSQL ofrece soporte completo para CTE recursivas con funcionalidades adicionales como tipos ARRAY y operadores de array.

• MySQL (desde la versión 8.0) soporta CTE recursivas, pero con algunas limitaciones en cuanto a funciones de manipulación de arrays y otras características avanzadas.

-- Ejemplo compatible con ambos sistemas
WITH RECURSIVE empleados_subordinados AS (
    -- Caso base: empleado específico
    SELECT id_empleado, nombre, id_jefe, 0 AS nivel
    FROM empleados
    WHERE id_empleado = 42
    
    UNION ALL
    
    -- Caso recursivo: todos los subordinados directos
    SELECT e.id_empleado, e.nombre, e.id_jefe, s.nivel + 1
    FROM empleados e
    JOIN empleados_subordinados s ON e.id_jefe = s.id_empleado
)
SELECT 
    id_empleado,
    nombre,
    nivel
FROM empleados_subordinados
ORDER BY nivel, nombre;

Casos de uso prácticos

Las CTE recursivas son especialmente útiles en estos escenarios:

• Estructuras jerárquicas: Organigramas, categorías anidadas, comentarios con respuestas.
• Análisis de grafos: Rutas entre nodos, distancias mínimas, detección de ciclos.
• Generación de datos: Series temporales, secuencias numéricas, calendarios.
• Cálculos iterativos: Algoritmos que requieren múltiples pasadas sobre los datos.

-- Ejemplo: Categorías de productos con su ruta completa
WITH RECURSIVE categoria_path AS (
    -- Categorías raíz
    SELECT 
        id_categoria, 
        nombre, 
        CAST(nombre AS VARCHAR(1000)) AS ruta_completa
    FROM categorias
    WHERE id_categoria_padre IS NULL
    
    UNION ALL
    
    -- Subcategorías
    SELECT 
        c.id_categoria,
        c.nombre,
        CONCAT(cp.ruta_completa, ' > ', c.nombre) AS ruta_completa
    FROM categorias c
    JOIN categoria_path cp ON c.id_categoria_padre = cp.id_categoria
)
SELECT 
    id_categoria,
    ruta_completa
FROM categoria_path
ORDER BY ruta_completa;

Este ejemplo muestra cómo construir la ruta completa de cada categoría, incluyendo todas sus categorías padre, lo que resulta muy útil para sistemas de navegación en sitios web de comercio electrónico.

Prevención de bucles infinitos

Un aspecto crítico al trabajar con CTE recursivas es evitar los bucles infinitos. Esto puede ocurrir si los datos contienen ciclos o si la condición de terminación no está bien definida:

WITH RECURSIVE deteccion_ciclos AS (
    -- Caso base
    SELECT 
        id_nodo, 
        id_nodo_conectado,
        ARRAY[id_nodo] AS ruta_visitada,
        FALSE AS tiene_ciclo
    FROM grafo
    WHERE id_nodo = 1
    
    UNION ALL
    
    -- Caso recursivo con detección de ciclos
    SELECT 
        g.id_nodo,
        g.id_nodo_conectado,
        dc.ruta_visitada || g.id_nodo_conectado,
        g.id_nodo_conectado = ANY(dc.ruta_visitada) AS tiene_ciclo
    FROM grafo g
    JOIN deteccion_ciclos dc ON g.id_nodo = dc.id_nodo_conectado
    WHERE NOT dc.tiene_ciclo
)
SELECT * FROM deteccion_ciclos
WHERE tiene_ciclo = TRUE
LIMIT 1;

Este ejemplo detecta ciclos en un grafo, utilizando un array para rastrear los nodos ya visitados en cada ruta.

Las CTE recursivas representan una herramienta fundamental para resolver problemas complejos que involucran estructuras jerárquicas o iterativas, permitiéndonos expresar soluciones elegantes a problemas que de otro modo requerirían múltiples consultas o incluso procesamiento externo a la base de datos.

CTE vs subconsultas

Las expresiones de tabla comunes (CTE) y las subconsultas son dos técnicas para estructurar consultas complejas en SQL, pero presentan diferencias significativas en términos de legibilidad, rendimiento y flexibilidad. Comprender cuándo utilizar cada una puede mejorar considerablemente la calidad de nuestro código SQL.

Diferencias fundamentales

Las subconsultas son consultas anidadas dentro de otra consulta, mientras que las CTE son bloques de consulta nombrados que se definen al principio mediante la cláusula WITH y pueden referenciarse posteriormente. Esta diferencia estructural tiene importantes implicaciones:

-- Ejemplo con subconsulta
SELECT 
    nombre_producto,
    precio,
    (SELECT AVG(precio) FROM productos) AS precio_promedio
FROM productos
WHERE precio > (
    SELECT AVG(precio) * 1.5 
    FROM productos
);

-- Mismo ejemplo con CTE
WITH precio_promedio AS (
    SELECT AVG(precio) AS valor
    FROM productos
)
SELECT 
    nombre_producto,
    precio,
    (SELECT valor FROM precio_promedio) AS precio_promedio
FROM productos, precio_promedio
WHERE precio > precio_promedio.valor * 1.5;

Legibilidad y mantenimiento

La legibilidad es quizás la ventaja más evidente de las CTE frente a las subconsultas:

• Estructura lineal vs. anidada: Las CTE siguen un flujo de lectura de arriba hacia abajo, mientras que las subconsultas requieren leer de adentro hacia afuera.

• Modularidad: Las CTE permiten dividir consultas complejas en bloques lógicos con nombres descriptivos.

• Reutilización: Una CTE puede referenciarse múltiples veces en la consulta principal sin duplicar código.

Veamos un ejemplo comparativo:

-- Con subconsultas anidadas (difícil de leer)
SELECT 
    d.nombre_departamento,
    (
        SELECT COUNT(*) 
        FROM empleados e 
        WHERE e.id_departamento = d.id_departamento
    ) AS total_empleados,
    (
        SELECT AVG(salario) 
        FROM empleados e 
        WHERE e.id_departamento = d.id_departamento
    ) AS salario_promedio
FROM departamentos d
WHERE (
    SELECT COUNT(*) 
    FROM empleados e 
    WHERE e.id_departamento = d.id_departamento
) > 5;

-- Con CTE (más legible)
WITH 
    empleados_por_depto AS (
        SELECT 
            id_departamento,
            COUNT(*) AS total_empleados,
            AVG(salario) AS salario_promedio
        FROM empleados
        GROUP BY id_departamento
    )
SELECT 
    d.nombre_departamento,
    e.total_empleados,
    e.salario_promedio
FROM departamentos d
JOIN empleados_por_depto e ON d.id_departamento = e.id_departamento
WHERE e.total_empleados > 5;

Rendimiento y optimización

En términos de rendimiento, la situación es más compleja y depende de varios factores:

• Materialización: En PostgreSQL, las CTE se materializan (se ejecutan una vez y almacenan sus resultados temporalmente), lo que puede ser beneficioso si la CTE se referencia múltiples veces, pero contraproducente si solo se usa una vez.

• Optimización de consultas: Las subconsultas a menudo pueden ser optimizadas por el motor de base de datos, mientras que las CTE materializadas pueden limitar ciertas optimizaciones.

• Control de materialización: PostgreSQL 12+ permite controlar la materialización con las cláusulas MATERIALIZED o NOT MATERIALIZED.

-- PostgreSQL: CTE con materialización explícita
WITH empleados_activos AS MATERIALIZED (
    SELECT * FROM empleados WHERE estado = 'activo'
)
SELECT * FROM empleados_activos WHERE salario > 50000;

-- PostgreSQL: CTE sin materialización
WITH empleados_activos AS NOT MATERIALIZED (
    SELECT * FROM empleados WHERE estado = 'activo'
)
SELECT * FROM empleados_activos WHERE salario > 50000;

En MySQL, las CTE no siempre se materializan, y el optimizador puede decidir integrarlas en la consulta principal si es más eficiente.

Casos de uso específicos

Existen situaciones donde una opción es claramente superior a la otra:

Cuando usar CTE:

• Consultas recursivas: Solo las CTE permiten recursión, como vimos en la sección anterior.

-- Imposible con subconsultas
WITH RECURSIVE numeros AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numeros WHERE n < 10
)
SELECT * FROM numeros;

• Consultas con múltiples pasos lógicos: Cuando necesitamos construir resultados intermedios que se utilizan en varios lugares.

WITH 
    ventas_mensuales AS (
        SELECT 
            producto_id,
            EXTRACT(MONTH FROM fecha_venta) AS mes,
            SUM(cantidad) AS unidades
        FROM ventas
        WHERE EXTRACT(YEAR FROM fecha_venta) = 2023
        GROUP BY producto_id, EXTRACT(MONTH FROM fecha_venta)
    ),
    estadisticas_producto AS (
        SELECT
            producto_id,
            AVG(unidades) AS promedio_mensual,
            MAX(unidades) AS mejor_mes,
            MIN(unidades) AS peor_mes
        FROM ventas_mensuales
        GROUP BY producto_id
    )
SELECT 
    p.nombre,
    e.promedio_mensual,
    e.mejor_mes,
    e.peor_mes
FROM productos p
JOIN estadisticas_producto e ON p.id = e.producto_id
ORDER BY e.promedio_mensual DESC;

• Mejora de legibilidad: Cuando la consulta es compleja y se beneficiaría de una estructura más clara.

Cuando usar subconsultas:

• Consultas simples: Para operaciones sencillas, las subconsultas pueden ser más directas.

-- Subconsulta simple y clara
SELECT nombre, apellido
FROM clientes
WHERE id NOT IN (SELECT cliente_id FROM pedidos WHERE YEAR(fecha) = 2023);

• Subconsultas correlacionadas: Cuando necesitamos referenciar la consulta externa desde la subconsulta.

-- Subconsulta correlacionada
SELECT 
    c.nombre_categoria,
    (
        SELECT COUNT(*) 
        FROM productos p 
        WHERE p.categoria_id = c.id
    ) AS total_productos
FROM categorias c;

• Optimización específica: Cuando sabemos que el optimizador puede manejar mejor una subconsulta en un caso particular.

Combinando ambas técnicas

En consultas complejas, a menudo es beneficioso combinar ambas técnicas:

WITH top_clientes AS (
    SELECT 
        cliente_id,
        SUM(total) AS gasto_total
    FROM facturas
    WHERE fecha_emision >= '2023-01-01'
    GROUP BY cliente_id
    ORDER BY gasto_total DESC
    LIMIT 100
)
SELECT 
    c.nombre,
    c.email,
    tc.gasto_total,
    (
        SELECT COUNT(DISTINCT producto_id) 
        FROM detalles_factura df
        JOIN facturas f ON df.factura_id = f.id
        WHERE f.cliente_id = c.id
    ) AS productos_diferentes
FROM clientes c
JOIN top_clientes tc ON c.id = tc.cliente_id
WHERE c.fecha_registro < (
    SELECT AVG(fecha_registro) FROM clientes
);

En este ejemplo, usamos una CTE para identificar los 100 clientes con mayor gasto, y luego utilizamos subconsultas para obtener información adicional en la consulta principal.

Impacto en el plan de ejecución

Para entender mejor las diferencias de rendimiento, es útil examinar los planes de ejecución:

-- Analizar plan con subconsulta
EXPLAIN ANALYZE
SELECT *
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos);

-- Analizar plan con CTE
EXPLAIN ANALYZE
WITH precio_promedio AS (
    SELECT AVG(precio) AS valor FROM productos
)
SELECT *
FROM productos, precio_promedio
WHERE precio > precio_promedio.valor;

En PostgreSQL, la versión con CTE probablemente mostrará un nodo adicional para la materialización, mientras que la subconsulta podría integrarse más directamente en el plan.

Consideraciones prácticas

Al decidir entre CTE y subconsultas, considera estos factores:

• Complejidad de la consulta: A mayor complejidad, más beneficios aportan las CTE.
• Frecuencia de referencia: Si necesitas referirte a los mismos resultados múltiples veces, las CTE son preferibles.
• Necesidad de recursión: Solo las CTE permiten consultas recursivas.
• Versión de la base de datos: Las versiones más recientes de MySQL y PostgreSQL tienen mejor soporte y optimización para CTE.
• Legibilidad del código: Las CTE generalmente producen código más mantenible en consultas complejas.

Migración de subconsultas a CTE

Convertir subconsultas a CTE suele mejorar la legibilidad. Veamos un ejemplo de migración:

-- Consulta original con subconsultas
SELECT 
    p.nombre_producto,
    p.precio,
    p.precio - (SELECT AVG(precio) FROM productos) AS diferencia_precio
FROM productos p
WHERE p.categoria_id IN (
    SELECT id 
    FROM categorias 
    WHERE nombre_categoria IN ('Electrónica', 'Informática')
)
AND p.proveedor_id IN (
    SELECT id 
    FROM proveedores 
    WHERE pais = 'España'
);

-- Versión mejorada con CTE
WITH 
    precio_promedio AS (
        SELECT AVG(precio) AS valor FROM productos
    ),
    categorias_tech AS (
        SELECT id 
        FROM categorias 
        WHERE nombre_categoria IN ('Electrónica', 'Informática')
    ),
    proveedores_locales AS (
        SELECT id 
        FROM proveedores 
        WHERE pais = 'España'
    )
SELECT 
    p.nombre_producto,
    p.precio,
    p.precio - pa.valor AS diferencia_precio
FROM productos p
CROSS JOIN precio_promedio pa
WHERE p.categoria_id IN (SELECT id FROM categorias_tech)
AND p.proveedor_id IN (SELECT id FROM proveedores_locales);

Resumen comparativo

En la práctica, las CTE han ganado popularidad por su capacidad para mejorar la legibilidad y mantenibilidad del código SQL, especialmente en consultas complejas. Sin embargo, las subconsultas siguen siendo útiles en muchos escenarios y a veces ofrecen ventajas de rendimiento.

La elección entre CTE y subconsultas no debe verse como excluyente, sino como parte del arsenal de técnicas que un desarrollador SQL debe dominar para escribir consultas eficientes y mantenibles según las necesidades específicas de cada situación.