CTEs com WITH — Subconsultas e CTEs

O problema: subconsultas aninhadas e ilegíveis

As subconsultas resolvem muito, mas quando se aninham se tornam difíceis de ler: é preciso ler de dentro para fora e a mesma subconsulta pode se repetir.

CTE: uma subconsulta com nome

Uma CTE (Common Table Expression) é uma subconsulta à qual você dá um nome com a cláusula WITH, antes do SELECT principal. Depois você a usa como se fosse mais uma tabela:

WITH precos_categoria AS (
  SELECT categoria_id, AVG(preco) AS media
  FROM produtos
  GROUP BY categoria_id
)
SELECT c.nome, pc.media
FROM precos_categoria pc
JOIN categorias c ON c.id = pc.categoria_id
ORDER BY pc.media DESC;

Vantagens:

Legibilidade: a consulta se lê de cima para baixo, como passos.
Reutilização: você pode referenciar a CTE várias vezes no mesmo SELECT sem repetir seu código.
Composição: você pode definir várias CTEs separadas por vírgulas, e umas podem se apoiar nas anteriores.

WITH
  caros AS (
    SELECT * FROM produtos WHERE preco > 100
  ),
  com_estoque AS (
    SELECT * FROM caros WHERE estoque > 0   -- se apoia na CTE anterior
  )
SELECT nome, preco, estoque FROM com_estoque ORDER BY preco DESC;

CTEs recursivas (menção)

Com WITH RECURSIVE uma CTE pode referenciar a si mesma, o que permite percorrer estruturas hierárquicas (uma árvore de categorias pai/filho, uma lista de funcionários e seus chefes) ou gerar séries de valores. É um tema avançado: por enquanto basta saber que existe e que é a ferramenta para dados em forma de árvore ou grafo, algo que as subconsultas normais não conseguem expressar.

Regra prática: se você se pegar copiando e colando a mesma subconsulta, ou aninhando três níveis, transforme-a em uma CTE com WITH.

Exemplos

WITH para dar nome a um cálculo intermediário

WITH stats AS (
  SELECT AVG(preco) AS media FROM produtos
)
SELECT nome, preco
FROM produtos, stats
WHERE preco > stats.media
ORDER BY preco DESC;

Várias CTEs encadeadas

WITH
  caros AS (SELECT * FROM produtos WHERE preco > 100),
  com_estoque AS (SELECT * FROM caros WHERE estoque > 0)
SELECT nome, preco FROM com_estoque ORDER BY preco DESC;