Consultas SQL avançadas: 10 recursos com exemplos

O cenário dos exemplos

Todos os exemplos de consultas SQL avançadas usam um esquema chamado sales, formado pelas tabelas orders e order_items. Assim, você consegue comparar os recursos sobre os mesmos dados.

Antes de começar, vale separar o que pertence ao padrão SQL do que é específico de um fornecedor. Essa diferença define o custo de uma eventual migração entre bancos.

Sete dos dez recursos de consultas SQL avançadas apresentados fazem parte do padrão. Por isso, as divergências de sintaxe, quando existem, costumam ser pontuais.

Uma recomendação prática antes de seguir: não adote os dez de uma vez. O caminho mais produtivo é identificar qual deles resolve um problema real do seu sistema agora.

1. CTEs em consultas SQL avançadas

Uma consulta complexa concentrada em uma única instrução é difícil de ler e ainda mais difícil de alterar meses depois. A Common Table Expression, ou CTE, resolve esse problema porque nomeia etapas intermediárias com a palavra-chave WITH.

Cada etapa se comporta como um resultado temporário nomeado. Dessa forma, as etapas seguintes se apoiam nas anteriores.

-- Pedidos dos últimos 30 dias consolidados por número
WITH recent_orders AS (
    SELECT o.id, o.number, o.channel, o.status, o.created_at
    FROM sales.orders o
    WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
),
summary AS (
    SELECT ro.number, ro.channel, ro.status,
        COUNT(oi.id) AS total_items,
        SUM(oi.quantity) AS total_quantity
    FROM recent_orders ro
    LEFT JOIN sales.order_items oi ON oi.order_id = ro.id
    GROUP BY ro.number, ro.channel, ro.status
)
SELECT number, channel, status, total_items, total_quantity
FROM summary
ORDER BY total_quantity DESC;

Com isso, a leitura passa a ser de cima para baixo, como uma sequência de passos. As subconsultas aninhadas, por outro lado, impõem uma leitura de dentro para fora.

Ainda assim, dois pontos costumam passar despercebidos. Primeiro, uma CTE também funciona com INSERT, UPDATE e DELETE, o que habilita escritas encadeadas com RETURNING.

O segundo ponto diz respeito ao desempenho. Desde o PostgreSQL 12, o planejador incorpora à consulta principal toda CTE sem efeitos colaterais que apareça uma única vez, e as palavras MATERIALIZED e NOT MATERIALIZED forçam ou impedem esse comportamento.

Por fim, a variação WITH RECURSIVE percorre estruturas hierárquicas. Organogramas, árvores de categorias e listas de materiais são os casos clássicos.

2. Funções de janela em consultas SQL avançadas

Certos cálculos precisam considerar um conjunto de linhas relacionadas sem descartar as linhas individuais. O GROUP BY não atende a esse caso, porque colapsa cada grupo em uma única linha.

Já as funções de janela calculam sobre um conjunto de linhas, chamado de janela, e mesmo assim preservam cada linha do resultado. Elas aparecem em quase toda lista de consultas SQL avançadas por esse motivo.

SELECT
    number,
    channel,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY channel ORDER BY created_at DESC) AS sequence,
    RANK() OVER (PARTITION BY channel ORDER BY created_at DESC) AS position,
    LAG(status) OVER (PARTITION BY channel ORDER BY created_at) AS previous_status,
    SUM(total_amount) OVER (
        PARTITION BY channel ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales.orders;

3. Junções LATERAL em consultas SQL avançadas

Uma junção convencional relaciona duas tabelas a partir de uma condição. Contudo, ela não executa uma consulta independente para cada linha do lado esquerdo.

Nas consultas SQL avançadas, a junção LATERAL remove essa restrição. Ela deixa a subconsulta da direita referenciar colunas da tabela da esquerda, executando uma vez por linha.

-- Os três pedidos mais recentes de cada canal
SELECT c.channel, o.number, o.status, o.created_at
FROM (SELECT DISTINCT channel FROM sales.orders) c
CROSS JOIN LATERAL (
    SELECT number, status, created_at
    FROM sales.orders
    WHERE channel = c.channel
    ORDER BY created_at DESC
    LIMIT 3
) o;

O detalhe determinante está em WHERE channel = c.channel, que enxerga o valor da linha externa. Esse é o padrão canônico para o problema de top N por grupo.

Além disso, o recurso invoca funções que retornam conjuntos, passando uma coluna da linha corrente como argumento. Quando a subconsulta não devolve nada, a variação LEFT JOIN LATERAL preserva as linhas da esquerda.

No SQL Server, por sua vez, CROSS APPLY e OUTER APPLY expressam a mesma semântica.

4. Relatórios com GROUPING SETS, ROLLUP e CUBE

Relatórios raramente exigem um único nível de agregação. O pedido típico envolve o total por canal e status, os subtotais por canal e por status e o total geral, tudo na mesma saída.

Normalmente, a solução ingênua costura várias consultas com UNION ALL e varre a mesma tabela repetidas vezes. Nesse caso, uma consulta SQL avançada com GROUPING SETS faz o trabalho em uma só passagem.

SELECT
    channel,
    status,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS amount
FROM sales.orders
GROUP BY GROUPING SETS (
    (channel, status),
    (channel),
    (status),
    ()
);

SELECT
    channel,
    status,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS total_orders,
    GROUPING(channel, status) AS level
FROM sales.orders
GROUP BY ROLLUP (channel, status, DATE_TRUNC('month', created_at));

Repare que o conjunto vazio () representa o total geral, que não agrupa por coluna alguma. ROLLUP, por sua vez, abrevia subtotais hierárquicos, porque avalia os prefixos da lista de colunas.

O CUBE vai além e gera todas as combinações possíveis das colunas. Ele serve bem para cubos analíticos de baixa cardinalidade.

Existe ainda a função GROUPING, que informa se um NULL do resultado veio dos dados ou da agregação. Sem essa distinção, formatar o relatório corretamente fica impossível.

Além da clareza, há um ganho concreto de desempenho. Afinal, o banco calcula todos os níveis em uma única passagem pelos dados.

5. FILTER em consultas SQL avançadas

Contar ou somar apenas as linhas que atendem a uma condição é uma necessidade recorrente. Isso fica mais evidente quando vários recortes precisam aparecer lado a lado.

Em consultas SQL avançadas, a cláusula FILTER aplica uma condição a uma agregação específica, sem afetar as demais.

SELECT
    channel,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
    SUM(total_amount) FILTER (WHERE status = 'delivered') AS delivered_amount,
    COUNT(*) FILTER (WHERE status = 'in_transit') AS in_transit,
    AVG(total_amount) FILTER (WHERE created_at >= CURRENT_DATE - 7) AS weekly_average_ticket
FROM sales.orders
GROUP BY channel;

Assim, o resultado sai em uma única varredura, e cada agregação enxerga o subconjunto que lhe interessa. Funcionalmente, o efeito equivale ao antigo SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END).

A diferença está na intenção explícita, que reduz erros de leitura e de manutenção. Também há uma distinção sutil, porque o COUNT(*) com FILTER conta só as linhas que satisfazem a condição, enquanto o CASE com ELSE 0 soma zeros e muda o comportamento diante de nulos.

Embora FILTER pertença ao padrão SQL, apenas o PostgreSQL e o SQLite o implementam. Portanto, no SQL Server e no MySQL, a alternativa continua sendo o CASE dentro da agregação.

6. UPSERT com INSERT ON CONFLICT

Inserir um registro novo ou atualizar o existente é um requisito corriqueiro que costuma virar código de aplicação. O padrão mais comum envolve uma leitura de verificação, uma condicional e dois caminhos de escrita.

Esse arranjo, no entanto, carrega uma condição de corrida entre a leitura e a gravação. O ON CONFLICT resolve o problema dentro do próprio banco.

ALTER TABLE sales.orders
    ADD CONSTRAINT orders_number_unique UNIQUE (number);

INSERT INTO sales.orders (id, number, channel, status, total_amount, updated_at)
VALUES (gen_random_uuid(), 'ORD-2026-0001', 'marketplace', 'pending', 249.90, NOW())
ON CONFLICT (number) DO UPDATE
SET channel = EXCLUDED.channel,
    status = EXCLUDED.status,
    total_amount = EXCLUDED.total_amount,
    updated_at = GREATEST(sales.orders.updated_at, EXCLUDED.updated_at)
-- Condição de guarda: pedidos cancelados não são alterados
WHERE sales.orders.status <> 'cancelled'
RETURNING id, number, status;

Primeiro, note que o banco detecta o conflito sobre uma restrição de unicidade, que precisa existir antes. Em seguida, a pseudotabela EXCLUDED expõe os valores que o comando tentou inserir.

A cláusula WHERE opcional no DO UPDATE funciona como guarda. Ela protege, por exemplo, registros em estados que ninguém deveria alterar.

Como a operação é atômica, essa consulta SQL avançada elimina a condição de corrida entre chamadas concorrentes. Além disso, o PostgreSQL suporta MERGE desde a versão 15, sintaxe do padrão também usada pelo SQL Server e pelo Oracle.

No MySQL, o equivalente é INSERT ... ON DUPLICATE KEY UPDATE.

7. JSON nativo em consultas SQL avançadas

Nem todo dado é naturalmente relacional. Corpos de webhook, eventos de auditoria e blocos de configuração, por exemplo, costumam ter formato variável.

A prática mais comum guarda esse conteúdo como texto e o processa na aplicação. Consequentemente, a equipe perde qualquer chance de indexar e filtrar no banco.

O PostgreSQL, por outro lado, guarda documentos no tipo JSONB, em formato binário já analisado. Com isso, as consultas SQL avançadas filtram o conteúdo diretamente, como descreve a documentação de tipos JSON do PostgreSQL.

CREATE TABLE sales.events (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);

CREATE INDEX idx_events_payload ON sales.events USING GIN (payload jsonb_path_ops);

SELECT
    payload ->> 'type' AS type,
    payload -> 'items' -> 0 ->> 'sku' AS first_sku,
    CAST(payload ->> 'amount' AS NUMERIC) AS amount
FROM sales.events
WHERE payload @> '{"type": "order_created"}';

O operador ->> extrai o valor como texto, ao passo que -> retorna um objeto ou elemento JSON e permite navegar por caminhos aninhados.

Já o operador de contenção @> verifica se o documento contém determinada estrutura. Aliás, é ele quem mais aproveita o índice GIN criado sobre a coluna.

A partir do PostgreSQL 17, a função JSON_TABLE converte um array JSON diretamente em linhas relacionais. Dessa forma, os dois mundos ficam ainda mais próximos.

No SQL Server, o suporte histórico vem de JSON_VALUE, JSON_QUERY e OPENJSON. Além disso, a versão 2025 trouxe o tipo nativo json, com armazenamento binário e leituras mais eficientes que nvarchar(max).

8. Colunas geradas

Quando o valor de uma coluna deriva sempre de outras, calculá-lo na aplicação obriga cada caminho de escrita a lembrar da fórmula. Basta um serviço novo, uma carga em lote ou um script de correção para a regra sair do lugar.

A coluna gerada evita esse risco e simplifica as consultas SQL avançadas, porque move a fórmula para a definição da tabela. Assim, o banco passa a ser o responsável pelo cálculo.

CREATE TABLE sales.order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id UUID NOT NULL REFERENCES sales.orders (id),
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    discount NUMERIC(5,4) NOT NULL DEFAULT 0,
    -- Calculada na leitura
    subtotal NUMERIC(12,2)
        GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL,
    -- Calculada na escrita e gravada em disco
    total NUMERIC(12,2)
        GENERATED ALWAYS AS (quantity * unit_price * (1 - discount)) STORED
);

Como a coluna gerada não aceita escrita direta, gravar um valor divergente da fórmula se torna impossível. Aqui, entretanto, existe uma mudança de versão que merece atenção.

Até a versão 17, o PostgreSQL só oferecia colunas STORED, que calculam o valor na escrita e ocupam espaço em disco. Já o PostgreSQL 18 introduziu as colunas virtuais e as tornou o padrão, segundo a documentação de colunas geradas.

Portanto, uma definição sem a palavra-chave explícita calcula o valor no momento da leitura. A escolha entre as duas formas é uma troca direta.

VIRTUAL não ocupa espaço e não onera a escrita. Por outro lado, STORED é obrigatória quando você precisa indexar a coluna ou replicá-la logicamente.

No SQL Server, o equivalente é a coluna computada, declarada como total AS (expression) e persistida com a palavra-chave PERSISTED.

9. Amostragem com TABLESAMPLE

Consultas exploratórias sobre tabelas muito grandes demoram mesmo quando o objetivo é só entender a forma dos dados. Para esse cenário existe o TABLESAMPLE, que devolve uma amostra aleatória lendo apenas uma fração da tabela.

SELECT channel, COUNT(*)
FROM sales.orders TABLESAMPLE SYSTEM (5)
GROUP BY channel;

-- A semente fixa garante a mesma amostra em cada execução
SELECT *
FROM sales.orders TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

O método SYSTEM sorteia páginas inteiras, o que é rápido, porém sensível a agrupamentos físicos dos dados. Já o BERNOULLI avalia linha a linha e entrega uma amostra mais uniforme, ainda que ao custo de uma leitura completa.

A cláusula REPEATABLE fixa a semente do sorteio. Com isso, a mesma amostra volta a cada execução, o que é indispensável para testes reproduzíveis.

Cabe uma ressalva importante: o banco aplica o WHERE depois da amostragem, e não antes. Ou seja, amostrar 5% e filtrar por um status raro pode não retornar linha alguma.

Na prática, os usos mais frequentes são validar consultas SQL avançadas antes de rodá-las na base completa, estimar a distribuição de valores e gerar massas de teste. O TABLESAMPLE faz parte do padrão, existe no PostgreSQL desde a versão 9.5 e também funciona no SQL Server.

10. Índices parciais para consultas SQL avançadas

Um índice sobre a tabela inteira consome armazenamento e encarece toda escrita, mesmo quando as consultas tocam só uma pequena fatia das linhas. Em contrapartida, o índice parcial cobre apenas as linhas que satisfazem uma condição.

Logo, a estrutura resultante fica menor, mais rápida de percorrer e mais barata de manter.

CREATE INDEX idx_orders_open
    ON sales.orders (channel, created_at)
    WHERE status IN ('pending', 'in_transit');

-- Unicidade apenas entre pedidos não excluídos logicamente
CREATE UNIQUE INDEX idx_orders_number_active
    ON sales.orders (number)
    WHERE deleted_at IS NULL;

Para que consultas SQL avançadas aproveitem o índice, porém, o planejador precisa reconhecer o predicado da consulta como um subconjunto do predicado do índice.

O segundo exemplo mostra um uso particularmente útil, a unicidade condicional. Nele, o número do pedido só precisa ser único entre os registros não excluídos, regra impossível com uma restrição UNIQUE convencional.

Esse padrão combina muito bem com exclusão lógica, tema do artigo sobre soft delete com ASP.NET e EF. Registros ativos e filas de processamento pendente também são subconjuntos quentes ideais.

No SQL Server, o recurso se chama índice filtrado e usa a mesma sintaxe de CREATE INDEX com WHERE. Contudo, o índice precisa ser não clusterizado, e o predicado aceita apenas comparações simples, sem colunas computadas.

Quando usar cada recurso de consultas SQL avançadas

A tabela abaixo resume o problema que cada recurso de consultas SQL avançadas resolve. Dessa forma, você identifica rapidamente por onde começar.

Se você usa um ORM, vale entender como ele traduz as consultas para SQL. O artigo sobre IEnumerable vs IQueryable no EF mostra onde o filtro realmente acontece.

Conclusão

Nenhum dos recursos apresentados é exótico ou experimental. Todos fazem parte do padrão SQL ou de implementações estáveis há várias versões, e já moram no banco que a sua aplicação usa.

O que eles têm em comum é mover regra e cálculo para onde os dados estão. Consequentemente, tráfego de rede, código de aplicação e superfície para inconsistência diminuem juntos.

Existe também um ganho menos visível, o de expressar intenção. Uma cláusula FILTER comunica melhor do que um CASE aninhado, assim como um ON CONFLICT comunica melhor do que uma verificação seguida de escrita.

A adoção de consultas SQL avançadas, no entanto, não precisa ser simultânea. Escolha o recurso que resolve um problema existente hoje, seja uma consulta difícil de manter, um relatório lento ou uma condição de corrida, e incorpore os demais à medida que os problemas aparecerem.

FAQ

As CTEs prejudicam o desempenho das consultas?

Até o PostgreSQL 11, sim, porque o banco sempre materializava a CTE. A partir da versão 12, o planejador incorpora CTEs simples à consulta principal, e MATERIALIZED ou NOT MATERIALIZED controlam esse comportamento.

Quando usar função de janela e quando usar LATERAL?

A função de janela é melhor quando o cálculo acompanha todas as linhas, como em acumulados. Já a junção LATERAL rende mais para buscar poucas linhas por grupo, porque o LIMIT interno interrompe a leitura mais cedo.

Índices parciais funcionam com qualquer consulta?

Não. O planejador só usa o índice quando prova que o predicado da consulta é um subconjunto do predicado do índice.

Vale a pena usar JSONB em vez de colunas relacionais?

Vale para dados genuinamente variáveis, como payloads externos. Atributos estáveis e consultados com frequência continuam melhores como colunas relacionais.

Devo escolher colunas geradas VIRTUAL ou STORED?

Use STORED quando precisar indexar, replicar logicamente ou ler a coluna com muita frequência. Prefira VIRTUAL quando a fórmula for barata e a prioridade for economizar espaço e escrita.

Esses recursos funcionam com o Entity Framework Core?

Parcialmente. Colunas geradas, índices parciais e UPSERT cabem em migrações e mapeamentos, enquanto funções de janela, LATERAL e FILTER costumam exigir SQL explícito ou visões mapeadas.

Dá para usar TABLESAMPLE em produção?

Dá, desde que o objetivo seja estimativa ou exploração. Evite o recurso quando o resultado precisar ser exato, porque a amostragem é probabilística.