CTE, subquery ou tabela temporária: qual usar para organizar uma query grande

Chega uma hora em que a query não cabe mais numa tacada só. Aí aparecem três caminhos para quebrá-la em pedaços — e a dúvida de sempre sobre qual escolher.

A boa notícia: a escolha segue duas perguntas simples.

Subquery: o pedaço que só serve ali

A subquery é um SELECT dentro do outro. Serve para um cálculo pontual, usado uma vez só:

SELECT nome, valor
FROM pedidos
WHERE valor > (SELECT AVG(valor) FROM pedidos)

Funciona bem enquanto é uma. O problema é quando viram três, aninhadas — a query passa a se ler de dentro para fora e ninguém mais acha onde começa.

CTE: o mesmo pedaço, com nome

A CTE (WITH) é a mesma ideia, só que nomeada e escrita antes:

WITH media AS (
  SELECT AVG(valor) AS valor_medio FROM pedidos
)
SELECT p.nome, p.valor
FROM pedidos p
CROSS JOIN media m
WHERE p.valor > m.valor_medio

Duas vantagens práticas: a query volta a se ler de cima para baixo, e o mesmo bloco pode ser referenciado mais de uma vez sem copiar e colar.

A CTE existe só enquanto a query roda. Terminou a query, sumiu.

Tabela temporária: o pedaço que precisa sobreviver

A tabela temporária é gravada de verdade e sobrevive entre queries — dura até o fim da sua sessão:

CREATE TEMP TABLE base_pedidos AS
SELECT ... FROM pedidos WHERE ...;

-- agora dá pra rodar várias queries em cima dela
SELECT COUNT(*) FROM base_pedidos;

Vale quando o mesmo recorte pesado vai alimentar várias queries diferentes, ou quando ele demora tanto que você não quer recalculá-lo a cada tentativa.

Como decidir

Duas perguntas resolvem:

• Uso quantas vezes dentro da mesma query? Uma → subquery. Mais de uma → CTE.
• Preciso do resultado depois que a query acabar? Sim → tabela temporária.

Pra lembrar

Na dúvida entre subquery e CTE, prefira a CTE: o custo é o mesmo e a query fica legível para quem herdar ela depois — inclusive você, daqui a três meses.

A tabela temporária é a exceção, não o padrão. Ela só se paga quando o mesmo recorte serve a várias perguntas.