Algumas perguntas levam duas etapas para serem respondidas. “Quais pedidos estão acima da média?” exige que você encontre a média primeiro e depois compare cada pedido com ela. Subconsultas SQL permitem escrever ambas as etapas como uma instrução.

Este tutorial cobre cada tipo usando duas pequenas tabelas. Executamos essas consultas no SQLite 3.50 e os resultados mostrados vêm dessa execução. A sintaxe de subconsulta padrão é amplamente suportada e apontamos os locais onde os bancos de dados diferem.

O que é uma subconsulta?

Uma subconsulta, também chamada de consulta interna ou aninhada, é uma instrução SELECT colocada dentro de outra instrução, entre colchetes. Conceitualmente, a consulta interna produz um resultado que a consulta externa utiliza.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

As tabelas de amostra

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Observe que Dev não tem pedidos. Esse detalhe é importante para vários exemplos abaixo.

Visão geral dos tipos de subconsulta

Subconsultas escalares

Uma subconsulta escalar retorna exatamente um valor, portanto você pode usá-la sempre que um único valor fizer sentido. Aqui está a consulta anterior, com seu resultado. A média de todos os pedidos é 980:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

Você não poderia escrever WHERE total > AVG(total), porque agregações não são permitidas diretamente em WHERE. A subconsulta contorna isso calculando o valor separadamente.

E se uma subconsulta “escalar” retornar várias linhas? A maioria dos bancos de dados, incluindo PostgreSQL, MySQL e SQL Server, gera um erro. SQLite não. Em nosso teste, ele usou silenciosamente apenas a primeira linha, o que poderia esconder um bug real, portanto não dependa desse comportamento.

DENTRO e NÃO DENTRO

Quando a consulta interna retornar uma lista, use IN. Isso encontra clientes com pelo menos um pedido acima de 1.000:

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN faz o oposto, encontrando clientes que nunca fizeram pedidos:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

Isso funciona aqui, mas NOT IN tem um caso extremo perigoso, que abordaremos abaixo.

Subconsultas em FROM

Uma subconsulta em FROM atua como uma tabela temporária, geralmente chamada de tabela derivada. É útil quando você precisa agregar duas vezes, como nesta consulta de gasto médio por cliente em cada cidade:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

A consulta interna totaliza cada cliente e a externa calcula a média desses totais. Dar um alias à tabela derivada é um bom hábito, pois alguns bancos de dados exigem isso. Se GROUP BY for novo para você, consulte SQL GROUP BY e HAVING.

Subconsultas em SELECT

Uma subconsulta escalar também pode adicionar uma coluna calculada. Este conta pedidos por cliente:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev aparece com zero. Uma junção interna teria descartado Dev completamente, porque Dev não possui linhas correspondentes. Você poderia obter o mesmo resultado com LEFT JOIN e GROUP BY, que explicamos em Explicação de junções SQL.

Subconsultas correlacionadas

O exemplo anterior também está correlacionado, pois a consulta interna refere-se a c.id da consulta externa. Uma subconsulta não correlacionada pode ser executada sozinha. Um correlacionado não pode, porque depende de um valor da linha externa atual.

Isso encontra pedidos acima da média do mesmo cliente:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Aqui está como lê-lo. Para o pedido 101, a consulta interna calcula a média dos pedidos do cliente 1 (1.200 e 800), o que resulta em 1.000. Como 1.200 é maior, a linha é mantida. Para o pedido 102, aplica-se a mesma média, mas 800 não é maior, por isso é descartado. O pedido único do cliente 2 é igual à sua própria média, portanto também é descartado.

“Uma vez por linha” descreve a lógica, e os otimizadores de consulta geralmente os reescrevem em algo muito mais eficiente. Ainda assim, vale a pena investigar uma subconsulta correlacionada lenta. Um índice na coluna interna, aqui orders.customer_id, pode fazer uma grande diferença. Consulte explicação dos índices SQL.

EXISTE e NÃO EXISTE

EXISTS faz uma pergunta sim ou não: a consulta interna retorna pelo menos uma linha? Pode parar na primeira partida, e o que você seleciona dentro não importa, e é por isso que as pessoas costumam escrever SELECT 1.

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

A armadilha NOT IN NULL

Este é um dos bugs de subconsulta mais comuns. Criamos uma tabela com um ID real e um NULL e, em seguida, executamos os dois formulários nela:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Por que o primeiro não retorna nada? id NOT IN (1, NULL) significa id <> 1 AND id <> NULL. Qualquer comparação com NULL é desconhecida e não verdadeira, portanto, toda a condição nunca pode ser verdadeira. Não há mensagem de erro nem aviso, apenas um resultado vazio que parece plausível.

Subconsulta vs JOIN vs CTE

Muitas consultas podem ser escritas de mais de uma maneira. Anteriormente, encontramos clientes com um pedido grande usando IN. Aqui está o mesmo resultado com uma junção:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Observe o DISTINCT. Uma junção pode produzir duplicatas se um cliente tiver diversas correspondências, enquanto IN e EXISTS não podem.

O desempenho varia de acordo com o banco de dados e os dados, e os otimizadores modernos geralmente produzem planos semelhantes. Portanto, escolha primeiro a forma mais clara e, em seguida, marque EXPLAIN se ela for lenta.

Erros comuns

• Usando = com um resultado de várias linhas. Use IN ou certifique-se de que a consulta interna retorne uma linha.
• Usando NOT IN em uma coluna anulável. Você pode perder silenciosamente todas as linhas.
• Usando aliases pouco claros. Em consultas correlacionadas, qualifique cada coluna ou ela poderá vincular-se à tabela errada.
• Aninhamento profundo. Três níveis são difíceis de ler, então converta-os em CTEs.
• Adicionar ORDER BY dentro. Geralmente não tem efeito no resultado externo e alguns bancos de dados o rejeitam.

Considerações finais

As subconsultas são uma forma natural de expressar perguntas em duas etapas. Comece com subconsultas escalares e IN, depois pratique as correlacionadas raciocinando através de uma única linha externa de cada vez. E lembre-se de NOT EXISTS sempre que NULLs forem possíveis. Para praticar, escreva uma pergunta de três maneiras, com uma subconsulta, uma junção e um CTE, e compare os resultados e a legibilidade.

Perguntas frequentes

Continue aprendendo