CTE vs subconsultas: ¿cuándo elegir cada una?

Ya sabemos que las CTE hacen el código más legible. ¿Pero siempre hay que usarlas? A veces una simple subconsulta es más rápida y fácil. Vamos a ver cuándo cada herramienta juega a tu favor y a aprender a elegir con cabeza.

Subconsultas: rápido y sencillo

Ya recuerdas que una subconsulta es SQL dentro de SQL. Se mete directamente en la consulta principal y se ejecuta "en el sitio". Perfecto para operaciones simples y de una sola vez:

-- Encontrar productos más caros que el precio medio
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Aquí la subconsulta calcula el precio medio una vez y listo. Sin estructuras extra.

Rendimiento: ¿quién es más rápido?

Las subconsultas suelen ganar en velocidad para operaciones simples. PostgreSQL puede optimizarlas "al vuelo", sobre todo si la subconsulta se ejecuta una sola vez:

-- Rápido: la subconsulta se ejecuta una vez
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

Las CTE por defecto se materializan — PostgreSQL primero calcula el resultado de la CTE, lo guarda como tabla temporal y luego lo usa. Esto puede hacer más lentas las consultas simples:

-- Más lento: la CTE se materializa en una tabla temporal
WITH latest_date AS (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

¡Pero ojo! Desde PostgreSQL 12 puedes controlar la materialización:

-- Forzar a NO materializar
WITH latest_date AS NOT MATERIALIZED (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Uso múltiple: aquí las CTE mandan

Cuando necesitas el mismo resultado intermedio varias veces, la CTE es insustituible:

-- Con subconsulta: repetimos la misma lógica dos veces
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') AS completed_orders,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') * 100.0 / COUNT(*) AS completion_rate
FROM orders;

-- Con CTE: calculamos una vez, usamos dos veces
WITH completed_orders AS (
    SELECT COUNT(*) AS count FROM orders WHERE status = 'completed'
)
SELECT
    co.count AS completed_orders,
    co.count * 100.0 / (SELECT COUNT(*) FROM orders) AS completion_rate
FROM completed_orders co;

Analítica compleja: la CTE gana por puntos

Para análisis de varios pasos, la CTE convierte el caos en orden. Mira este informe de ventas:

Con subconsultas (un lío mental):

SELECT 
    category,
    revenue,
    revenue * 100.0 / (
        SELECT SUM(p.price * oi.quantity)
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        JOIN orders o ON oi.order_id = o.order_id
        WHERE EXTRACT(year FROM o.order_date) = 2024
    ) AS revenue_share
FROM (
    SELECT 
        p.category,
        SUM(p.price * oi.quantity) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
    GROUP BY p.category
) category_revenue;

Con CTE (todo ordenadito):

WITH yearly_sales AS (
    SELECT 
        p.category,
        p.price * oi.quantity AS sale_amount
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
),
category_revenue AS (
    SELECT 
        category,
        SUM(sale_amount) AS revenue
    FROM yearly_sales
    GROUP BY category
),
total_revenue AS (
    SELECT SUM(sale_amount) AS total FROM yearly_sales
)
SELECT 
    cr.category,
    cr.revenue,
    cr.revenue * 100.0 / tr.total AS revenue_share
FROM category_revenue cr, total_revenue tr;

Recursividad: monopolio de las CTE

Para estructuras jerárquicas, las subconsultas no sirven.

Sólo las CTE recursivas pueden con tareas tipo "encontrar todos los subordinados de un manager":

WITH RECURSIVE employee_hierarchy AS (
    -- Empezamos con el CEO
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Añadimos los subordinados de cada nivel
    SELECT e.employee_id, e.manager_id, e.name, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

Depuración y mantenimiento del código

Las CTE se pueden depurar fácilmente por partes:

-- Comprobamos la primera etapa
WITH active_customers AS (
    SELECT customer_id FROM customers WHERE status = 'active'
)
SELECT COUNT(*) FROM active_customers; -- Nos aseguramos de que la lógica es correcta

-- Añadimos la segunda etapa
WITH active_customers AS (...),
recent_orders AS (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT COUNT(*) FROM recent_orders; -- Comprobamos también esta etapa

Las subconsultas son más difíciles de depurar — tienes que sacarlas de contexto.

Recomendaciones prácticas

Usa subconsultas cuando:

• La lógica es simple y cabe en una línea
• Necesitas el máximo rendimiento en operaciones sencillas
• El resultado intermedio se usa solo una vez
• Trabajas con volúmenes pequeños de datos

Usa CTE cuando:

• La consulta es compleja y se puede dividir en etapas lógicas
• Necesitas usar varias veces los resultados intermedios
• Te importa la legibilidad y el mantenimiento del código
• Trabajas con jerarquías (CTE recursivas)
• Depuras lógica compleja por partes

Regla de oro

Empieza con una subconsulta. Si se vuelve difícil de leer o la lógica se repite — pásate a CTE. ¡Tu futuro compi (o tú mismo dentro de seis meses) te lo va a agradecer!