Subqueries e CTEs (WITH)

Às vezes uma consulta depende do resultado de outra consulta. "Produtos mais caros que a média" não dá pra responder com um número fixo no WHERE — a média em si já é uma consulta. Uma subquery é exatamente isso: uma consulta SQL dentro de outra.

Subquery no WHERE — um valor calculado antes de comparar

SELECT nome, preco
FROM produtos
WHERE preco > (SELECT AVG(preco) FROM produtos);

A subquery entre parênteses roda primeiro, calcula um único valor (a média de todos os preços), e só depois a consulta de fora compara o preço de cada linha contra esse valor. Não tem nada de mágico — é sequencial: primeiro resolve o que está dentro dos parênteses, depois usa o resultado.

Subquery no FROM — tratar um resultado como se fosse uma tabela

SELECT categoria, media_preco
FROM (
  SELECT categoria, AVG(preco) AS media_preco
  FROM produtos
  GROUP BY categoria
) AS resumo_categoria
WHERE media_preco > 100;

Aqui a subquery entre parênteses vira uma tabela temporária (por isso precisa de um apelido, AS resumo_categoria), e a consulta de fora filtra em cima dela. Funciona, mas repare que já ficou mais difícil de ler — e isso só piora se você precisar de uma subquery dentro da subquery.

CTE (WITH) — o mesmo resultado, com nome

WITH resumo_categoria AS (
  SELECT categoria, AVG(preco) AS media_preco
  FROM produtos
  GROUP BY categoria
)
SELECT categoria, media_preco
FROM resumo_categoria
WHERE media_preco > 100;

Uma CTE (Common Table Expression, declarada com WITH) faz exatamente o que a subquery no FROM fazia — só que com nome, declarada antes da consulta principal em vez de aninhada dentro dela. O banco executa do mesmo jeito; a diferença é só legibilidade: você lê de cima pra baixo, um passo por vez, em vez de decifrar parênteses aninhados.

Quando a CTE vale mais que a subquery

Pra uma comparação simples como a primeira desta lição, subquery no WHERE já é suficiente — não vale complicar. A CTE compensa quando você tem vários passos encadeados, ou quando o mesmo resultado intermediário seria reaproveitado mais de uma vez na consulta: nomear o passo deixa claro o que cada parte da consulta representa, em vez de forçar quem lê a montar mentalmente uma subquery dentro de outra dentro de outra.