CTE no SQLite: como usar a cláusula WITH

CTE no SQLite é uma subquery nomeada

Uma CTE (Common Table Expression) no SQLite nada mais é do que uma subquery que você tirou de dentro da consulta e deu um nome. Em vez de aninhar um SELECT dentro de outro SELECT, você declara a consulta lá em cima com WITH, dá um nome a ela e depois usa esse nome na query principal como se fosse uma tabela.

A estrutura é sempre a mesma:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

Leia de cima para baixo: primeiro construímos um resultado nomeado chamado customer_totals e, em seguida, consultamos esse resultado. A CTE funciona como uma view temporária que só existe durante a execução desse comando.

A mesma query sem CTE

Veja a mesma lógica escrita como subquery, para você comparar o que a CTE está substituindo:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

Mesma resposta. Mas repare na ordem de leitura: seu olho precisa mergulhar nos parênteses, entender o que está sendo calculado e depois voltar. Já a versão com CTE se lê na ordem em que o trabalho acontece — primeiro você define o resultado intermediário, depois usa ele. Numa query pequena, dá no mesmo. Numa query com três ou quatro etapas, é a diferença entre um código que dá pra bater o olho e entender e um que você precisa decifrar.

Múltiplos CTEs em uma só query

Dá pra encadear vários CTEs na mesma query, separados por vírgula. Cada um pode referenciar os que vieram antes dele, montando um pipeline de etapas nomeadas:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

Um único WITH, seguido das definições de CTEs separadas por vírgula. A segunda CTE (big_spenders) lê da primeira (customer_totals) como se estivesse lendo de uma tabela. O SELECT principal vem depois da última CTE.

Um deslize comum: escrever WITH de novo antes da segunda CTE. Não faça isso — é erro de sintaxe. Um WITH só já cobre todas.

Referenciando uma CTE em mais de um lugar

É aqui que as CTEs deixam as subqueries comendo poeira. Quando você precisa do mesmo resultado intermediário em dois pontos da query, a CTE permite calcular uma vez e reaproveitar quantas vezes quiser:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

A CTE é referenciada duas vezes: uma para calcular a média e outra como fonte principal. Sem a CTE, você teria que duplicar a query com GROUP BY, e qualquer alteração precisaria ser feita em dois lugares.

Usando CTE com INSERT, UPDATE e DELETE

CTEs não servem só para SELECT. Dá para colocar uma cláusula WITH antes de INSERT, UPDATE ou DELETE e aproveitar uma subquery nomeada em operações de escrita:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

O CTE descreve quais linhas marcar. O INSERT ... SELECT o utiliza como fonte. O mesmo truque funciona com DELETE FROM ... WHERE id IN (SELECT id FROM cte), útil para exclusões em etapas em que a lógica de seleção é mais complexa.

Quando vale a pena usar um CTE no SQLite

Algumas regras práticas:

• A consulta tem mais de uma etapa lógica. Agregar, depois filtrar pelo agregado e então fazer um join com o resultado — isso é um pipeline, e um CTE por etapa deixa tudo mais legível.
• Você teria que repetir a mesma subquery. Defina uma vez e referencie duas.
• A subquery merece um nome. Se você colocaria um comentário acima dela explicando o que representa, o nome do CTE é esse comentário — e a sintaxe garante que ele esteja lá.
• Você está prestes a escrever uma query recursiva. Isso só é possível com WITH RECURSIVE — assunto da próxima página.

Quando não vale a pena:

• Uma única subquery simples usada em um só lugar. WHERE id IN (SELECT id FROM ...) já resolve.
• Consultas em que performance é crítica e você já confirmou que escrever a lógica direto ajuda. O SQLite costuma tratar o CTE como uma barreira de otimização menos rígida do que outros bancos, mas, em caminhos quentes, vale checar com EXPLAIN QUERY PLAN.

Um exemplo prático

Juntando tudo — um pequeno relatório que descobre o maior pedido de cada cliente e como ele se compara à média do próprio cliente:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

Dois CTEs, cada um fazendo uma coisa só. O SELECT principal cuida da formatação do resultado. Dá pra ler a query de cima para baixo e entender cada etapa de forma independente — e é exatamente esse o propósito dos CTEs.

Próximo: CTEs recursivos

Até aqui, vimos apenas CTEs comuns — uma subquery nomeada, avaliada uma única vez. Mas o SQLite também aceita WITH RECURSIVE, em que o CTE faz referência a si mesmo para percorrer hierarquias, gerar sequências ou navegar por grafos. É esse o tema da próxima página.