SQL: CTEs (WITH) para organizar queries complexas

CTEs (Common Table Expressions) transformam queries ilegíveis em blocos organizados. Firebird suporta CTEs desde a versão 2.1.

Sintaxe básica

WITH clientes_ativos AS (
  SELECT id, nome, data_cadastro
  FROM clientes
  WHERE ativo = 1
)
SELECT ca.nome, COUNT(p.id) AS total_pedidos
FROM clientes_ativos ca
LEFT JOIN pedidos p ON p.cliente_id = ca.id
GROUP BY ca.nome;

Múltiplas CTEs

WITH
  pedidos_mes AS (
    SELECT cliente_id, SUM(valor) AS total
    FROM pedidos
    WHERE EXTRACT(MONTH FROM data_pedido) = EXTRACT(MONTH FROM CURRENT_DATE)
    GROUP BY cliente_id
  ),
  clientes_vip AS (
    SELECT id, nome FROM clientes WHERE vip = 1
  )
SELECT cv.nome, COALESCE(pm.total, 0) AS total_mes
FROM clientes_vip cv
LEFT JOIN pedidos_mes pm ON pm.cliente_id = cv.id;

CTE recursiva (hierarquias)

WITH RECURSIVE hierarquia AS (
  -- Base: nós raiz
  SELECT id, nome, pai_id, 0 AS nivel
  FROM categorias
  WHERE pai_id IS NULL

  UNION ALL

  -- Recursão: filhos
  SELECT c.id, c.nome, c.pai_id, h.nivel + 1
  FROM categorias c
  JOIN hierarquia h ON h.id = c.pai_id
)
SELECT * FROM hierarquia ORDER BY nivel, nome;

Quando usar CTE vs subquery

Cenário	Use CTE	Use subquery
Reutilizar resultado 2x+	✅	❌
Hierarquia/recursão	✅	❌
Query simples de 1 uso	❌	✅
Performance crítica	Teste ambos	Teste ambos

Dica de performance no Firebird

CTEs não são materializadas por padrão no Firebird — elas são inlined. Se a CTE for usada múltiplas vezes, pode ser mais lenta que uma tabela temporária. Teste com EXPLAIN PLAN.

Queries complexas com CTEs são difíceis de escrever à mão. Descreva a lógica em português no Sqlify e receba a query com CTEs organizadas.

Conclusão

CTEs tornam queries complexas legíveis e manuteníveis. Use para hierarquias (recursivas) e para evitar subqueries repetidas.