CTE (Common Table Expression): Deixe seu código SQL mais fácil de ler

Neste artigo vamos falar sobre CTE (Common Table Expression), um recurso do SQL que permite escrever cada parte de uma consulta separadamente, com um nome, antes da consulta principal começar.

Para entender a utilidade deste recurso, veja a consulta abaixo:

SELECT cliente_id, SUM(p.quantidade * pr.preco) AS total_gasto
FROM pedidos p
JOIN produtos pr ON p.produto_id = pr.id
GROUP BY cliente_id
HAVING SUM(p.quantidade * pr.preco) > (
    SELECT AVG(total)
    FROM (
        SELECT SUM(p2.quantidade * pr2.preco) AS total
        FROM pedidos p2
        JOIN produtos pr2 ON p2.produto_id = pr2.id
        GROUP BY p2.cliente_id
    ) AS gastos
)
ORDER BY total_gasto DESC, cliente_id ASC;

Essa consulta retorna os clientes que gastaram acima da média de gastos de todos os clientes. Para entender o que ela faz, é preciso começar pelo SELECT mais interno, calcular o total gasto por cliente, subir um nível para calcular a média desses totais e só então ler o SELECT externo que usa essa média como filtro.

Quando uma consulta cresce e passa a ter mais de uma subconsulta, o código cresce para dentro com mais níveis de SELECT aninhados. Encontrar onde cada parte começa e o que ela faz passa a exigir mais atenção a cada nível.

O CTE resolve esse problema.

Subconsultas com nome próprio

Os exemplos deste artigo usam o banco de uma loja com três tabelas: produtos, clientes e pedidos.