CTE vs subqueries: quando usar cada um?

A gente já sabe que CTE deixa o código mais legível. Mas será que sempre vale a pena usar? Às vezes uma subquery simples resolve melhor e mais rápido. Bora ver quando cada ferramenta brilha e aprender a escolher de forma consciente.

Subqueries: rápido e fácil

Você já lembra que subquery é SQL dentro do SQL. Ela fica embutida direto na query principal e roda "ali na hora". É ótima pra operações simples e de uso único:

-- Encontrar produtos mais caros que o preço médio
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Aqui a subquery calcula o preço médio uma vez só, e pronto. Sem firulas.

Performance: quem é mais rápido?

Subqueries geralmente ganham em velocidade pra operações simples. O PostgreSQL consegue otimizar elas "na hora", principalmente quando a subquery roda só uma vez:

-- Rápido: a subquery roda uma vez só
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

CTE por padrão são materializadas — o PostgreSQL primeiro calcula o resultado da CTE, salva como tabela temporária, e só depois usa. Isso pode deixar queries simples mais lentas:

-- Mais lento: a CTE vira uma tabela temporária
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;

Mas! A partir do PostgreSQL 12 dá pra controlar a materialização:

-- Forçar a NÃO 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;

Reuso: aqui a CTE manda bem

Quando você precisa do mesmo resultado intermediário várias vezes, a CTE vira indispensável:

-- Com subquery: repete a lógica duas vezes
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;

-- Com CTE: calcula uma vez, usa duas
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;

Análise complexa: CTE ganha fácil

Pra análise em várias etapas, CTE transforma bagunça em organização. Olha só esse relatório de vendas:

Com subqueries (vira uma confusão):

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;

Com CTE (tudo organizadinho):

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;

Recursão: só CTE resolve

Pra estruturas hierárquicas, subqueries não dão conta.

Só CTE recursiva resolve tarefas tipo "achar todos os subordinados de um gerente":

WITH RECURSIVE employee_hierarchy AS (
    -- Começa pelo CEO
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Adiciona os subordinados de cada nível
    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;

Debug e manutenção do código

CTE é fácil de debugar por partes:

-- Testa a primeira etapa
WITH active_customers AS (
    SELECT customer_id FROM customers WHERE status = 'active'
)
SELECT COUNT(*) FROM active_customers; -- Confere se a lógica tá certa

-- Adiciona a 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; -- Testa essa etapa também

Subqueries são mais chatinhas de debugar — tem que tirar elas do contexto.

Dicas práticas

Use subqueries quando:

• A lógica é simples e cabe numa linha só
• Você quer máxima performance pra operações simples
• O resultado intermediário é usado só uma vez
• Tá lidando com pouco dado

Use CTE quando:

• A query é complexa e pode ser dividida em etapas lógicas
• Precisa usar resultados intermediários várias vezes
• Legibilidade e manutenção do código são importantes
• Tá trabalhando com hierarquias (CTE recursiva)
• Quer debugar lógica complexa por partes

Regra de ouro

Começa com subquery. Se ficar difícil de ler ou a lógica começar a se repetir — parte pra CTE. O seu colega do futuro (ou você mesmo daqui a seis meses) vai agradecer!